Help with Group By

• pramen

  SSC Enthusiast

  Points: 172

  More actions

  October 23, 2009 at 9:56 am

  #136634

  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

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 23, 2009 at 10:09 am

  #1070146

  What do you want to do if you have two different ItemNumbers with the highest cost for 1 item?

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• pramen

  SSC Enthusiast

  Points: 172

  More actions

  October 23, 2009 at 10:18 am

  #1070151

  Doesn't matter. Any item number is ok.

• mcha7628

  SSC Veteran

  Points: 265

  More actions

  October 23, 2009 at 11:01 am

  #1070175

  declare @t table (ID int, Cost int, ItemNumber nvarchar(2))

  insert into @t

  select 1, 50, 'A' union all

  select 1, 40, 'A' union all

  select 1, 50, 'B' union all

  select 2, 30, 'D' union all

  select 2, 30, 'EE' union all

  select 3, 70, 'F' union all

  select 3, 100, 'C' union all

  select 3, 100, 'C' union all

  select 3, 50, 'N'

  go;

  with test as

  (

  select id, max(cost) as cost

  from @t

  group by id

  )

  select distinct t.id, t.cost, max(t.itemnumber)

  from @t t inner join

  test on t.id = test.id and t.cost = test.cost

  group by t.id, t.cost

  order by id

  or

  with test as

  (

  select *, row_number() over(partition by id order by cost desc) as ranking

  from @t

  )

  select id, cost, itemnumber

  from test

  where ranking = 1