October 23, 2009 at 9:55 am
Please help me out here best process with t-sql or ssis.
I want unique ID with max cost and assosiate item number from below.
IDCostItemNumber
150A
140A
150B
230D
230EE
370F
3100C
3100C
350N
So if I do Group By on ID and get max cost then how can I assosiate Itemnumber with that record?
For the same ID and Cost - Item number can be same or different.
Thanks
October 23, 2009 at 10:36 am
If there is more than one item number, as in the case of
2 30 D
2 30 EE
which item number would you want to select? D, EE or does not matter?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 23, 2009 at 10:40 am
doesn't matter
October 23, 2009 at 10:49 am
OK, try
select ID, Max(Cost), Max(ItemNumber)
from ...
Group By ID
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 23, 2009 at 11:10 am
No...it doesn't work.
I need item number of the same row which got max cost.
For ex : for this set of data
100004 0.507850786
1000040.258290696
1000040.260549999
1000040.397850786
result :
1000040.507850786
BUT, above query returning
1000040.508290696
October 23, 2009 at 11:23 am
OK, if your table is called t1, this should do it:
with t as (
select distinct ID, Max(Cost) Cost
from T1
group by ID
)
select t1.ID, max(t1.Cost), Max(t1.ItemNumber) from T1
join T on T1.ID = t.ID and T1.Cost = T.Cost
group by t1.id
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply