SQL Newbie here... how would u solve for this

Question

SQL Newbie here... how would u solve for this

moe_mayne

SSCommitted

Points: 1933
More actions
July 21, 2005 at 4:52 pm

#165402

I have to have 1 row come back but as you can see by the pic its a bit tricky.. i am trying to merge the ApplicationID's together... any help i would be most greatful... thanks

Moe C

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Pic is a broken link.

When you post Please post your query, a sample table definition, some sample data, and what you would like the results to look like.

http://www.aspfaq.com/etiquette.asp?id=5006

moe_mayne SSCommitted Points: 1933 More actions · Answer 2

Sorry about that... i had to look at the HTML they had on here.... i hope this helps.. thanks

Moe C

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Okay,

There are 2 rows with application id 6004701.

Which one do you want returned?, The first one, the last one?

What columns from this table do you want returned?

moe_mayne SSCommitted Points: 1933 More actions · Answer 4

I want to merge them... as u see the ScoreModelType and ScoreType have different Values... i have been stuck on this for the last week trying to figure out some way... and i have to also keep in mind that performance is KEY....

Moe C

David Webb-CDS SSCoach Points: 17398 More actions · Answer 5

I'm a little confused as to what 'merge them' means in this context. Could you post an example of the output you want to see? Do you want both values of ScoreModelType and ScoreType to appear in the output?

And then again, I might be wrong ...
David Webb

moe_mayne SSCommitted Points: 1933 More actions · Answer 6

I have been trying to get One Row back... i just want one Application ID but with all the ScoreModelType info and Score info on the same line alaised something else....

Moe C

philcart SSC-Forever Points: 47794 More actions · Answer 7

Moe

To avoid any confusion,and allow people to help you in the right direction, is it possible for you to write/type out exactly what your expected output should look like. Once we have a start and a finish, building the code in-between can be pretty straightforward.

--------------------
Colt 45 - the original point and click interface

Ray M SSC-Insane Points: 21093 More actions · Answer 8

I talked w/ him via messenger, and I believe what he wants is

create table TestTable (AppSourceId int, appID int, SourceTypeID tinyint, score smallint, ScoreModelTypeID tinyint, Createdon Datetime)

truncate table TestTable

Insert into TestTable (AppSourceId,appID,SourceTypeID,score,ScoreModelTypeID,Createdon)

select 1, 6004701, 1, 0, 32, '06/27/2005 14:20:55'

union

select 2, 6004701, 1, 693, 17, '06/27/2005 14:20:55'

union

select 3, 6004706, 1, 1011, 32, '06/27/2005 14:25:55'

union

select 4, 6004706, 1, 656, 30, '06/27/2005 14:25:55'

union

select 5, 6004706, 2, 672, 2, '06/27/2005 14:25:55'

union

select 6, 6004706, 2, 983, 3, '06/27/2005 14:25:55'

union

select 7, 6004705, 1, 788, 32, '06/27/2005 14:30:55'

union

select 8, 6004705, 2, 274, 3, '06/27/2005 14:30:55'

AppID, AScore1, ScoreModelTypeID1, AScore2, ScoreModelTypeID2, CScore1, CScoreModelTypeid1, CScore2, CScoreModelTypeid2

6004701 0 32 693 17 Null Null Null Null

6004706 1011 32 656 30 675 2 983 3

6004705 788 32 Null Null 274 3 Null Null

Case SourceTypeID when 1 then AScore

SourceTypeID when 2 then cScore

I can't figure a way.

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 9

Using Ray's table definition, maybe something like this is what you want?

select t1.appID, t1.score as score1, t2.score as score2 from

TestTable t1 inner join TestTable t2

on t1.appID = t2.appID

where

t1.appSourceId = (select min(AppSourceId) from TestTable t3 where t3.appID = t1.appID)

and t2.appSourceId = (select min(AppSourceId) from TestTable t4 where t4.appID = t1.appID and t4.AppSourceId > t1.appSourceId)

On Ray's data, this returns

appID score1 score2

----------- ------ ------

6004701 0 693

6004706 1011 656

6004705 788 274

Ray, thanks for providing table definition and data that can be copied and pasted directly into query analyzer. Saves a lot of time

Ray M SSC-Insane Points: 21093 More actions · Answer 10

Not quite, Notice on 6004706, there are 4 records, 2 w/ sourece iD 1, and 2 with sourceId 2. output needs 2 more columns, if no cousreid 2, then these columns will be null, if there are rows with sourceid of 2 then populate.

Refere to requested output on previous post, I don't see a way to do it.

moe_mayne SSCommitted Points: 1933 More actions · Answer 11

This part will not work... the select stament can not be like this ... Plus a Union will kill the This large table... i have to take in account the Performace ....

select 1, 6004701, 1, 0, 32, '06/27/2005 14:20:55'

union

select 2, 6004701, 1, 693, 17, '06/27/2005 14:20:55'

union

select 3, 6004706, 1, 1011, 32, '06/27/2005 14:25:55'

union

select 4, 6004706, 1, 656, 30, '06/27/2005 14:25:55'

union

select 5, 6004706, 2, 672, 2, '06/27/2005 14:25:55'

union

select 6, 6004706, 2, 983, 3, '06/27/2005 14:25:55'

union

select 7, 6004705, 1, 788, 32, '06/27/2005 14:30:55'

union

select 8, 6004705, 2, 274, 3, '06/27/2005 14:30:55'

It think it would have to have to be something like

select 9,ApplicationID,ScoreTypeID,Score,ScoreModelType,CreatedOn

UNION

But this will never work

it cannot be tied down to the acual Application number...

The only other Solution i can think is Either Looping it or put it in a Cursor.. i am trying to avoid that cause also of the performance hit there....

Last resort is change the way the records are being stored... i have to talk to the other developers to do that and then they would have to change there way....AHHHHHHHHHHHH

Moe C

Ray M SSC-Insane Points: 21093 More actions · Answer 12

>select 1, 6004701, 1, 0, 32, '06/27/2005 14:20:55'

union

select 2, 6004701, 1, 693, 17, '06/27/2005 14:20:55'

union

select 3, 6004706, 1, 1011, 32, '06/27/2005 14:25:55'

union

select 4, 6004706, 1, 656, 30, '06/27/2005 14:25:55'

union

select 5, 6004706, 2, 672, 2, '06/27/2005 14:25:55'

union

select 6, 6004706, 2, 983, 3, '06/27/2005 14:25:55'

union

select 7, 6004705, 1, 788, 32, '06/27/2005 14:30:55'

union

select 8, 6004705, 2, 274, 3, '06/27/2005 14:30:55'<

I created All this to create a table with sample data for us to use to query on.

Its hard to write, and test a query without a table and data.

That was not part of the solution.

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 13

Ray, I was a little puzzled about the number 675 in your requested output, but if it should be 672 instead, then the following should work:

select

row1.AppID as AppID,

row1.score as AScore1,

row1.ScoreModelTypeID as ScoreModelTypeID1,

row2.score as AScore2,

row2.ScoreModelTypeID as ScoreModelTypeID2,

row3.score as CScore1,

row3.ScoreModelTypeID as CScoreModelTypeID1,

row4.score as CScore2,

row4.ScoreModelTypeID as CScoreModelTypeID2

from TestTable row1

left join TestTable row2

on row1.appID = row2.appID and row2.AppSourceId =

(select min(AppSourceId) from TestTable

where appID = row1.appID and AppSourceId > row1.AppSourceId and SourceTypeID = 1)

left join TestTable row3

on row1.appID = row3.appID and row3.AppSourceId =

(select min(AppSourceId) from TestTable

where appID = row1.appID and SourceTypeID = 2)

left join TestTable row4

on row1.appID = row4.appID and row4.AppSourceId =

(select min(AppSourceId) from TestTable

where appID = row1.appID and AppSourceId > row3.AppSourceId and SourceTypeID = 2)

where row1.AppSourceId =

(select min(AppSourceId) from TestTable where appID = row1.appID and SourceTypeID = 1)

Moe, note that this requires that for each appID there is a row with SourceTypeID 1, as in your sample data. If this is not the case, please let me know and I will fix the query. Alos please let me know if you have further problems, I am still not sure I have the correct output. Finally, I have no idea how this query performes, but I believe that the table can be equipped with indexes such that performance is OK.

Good luck!

moe_mayne SSCommitted Points: 1933 More actions · Answer 14

There will always be a ScoreTypeID.... and there will Always be a ScoreModelTypeID as well... I am checking the data right now... everything is looking like its coming in greattt....

Moe C