SQL challenge in Select statement

• Digs

  SSCertifiable

  Points: 7333

  More actions

  November 20, 2006 at 11:07 pm

  #175460

  My data, in SQL 2000

  A  2000

  A  3000

  A  1000

  A  1500

  B  1000

  C 5000

  C 1000

   

  How do I do this below,for A the max of the values is 3000, B is 1000 and C is 5000

  A 3000 3000

  A 2000 3000

  A 1500 3000

  A 1000 3000

  B 1000 1000

  C 3000 5000

  C 1000 5000

   

   

   

   

   

   

• yousef Ekhtiari

  Ten Centuries

  Points: 1293

  More actions

  November 21, 2006 at 12:22 am

  #673132

  Hi Digs

  consider the following table:

  create table #test(id char(1),value int)

  insert #test

  select 'A',  2000

  union all

  SELECT 'A',3000

  union all

  SELECT 'A',1000

  union all

  SELECT 'A',1500

  union all

  SELECT 'B',1000

  union all

  SELECT 'C',5000

  union all

  SELECT 'C',1000

  I think this is what you need:

  select id,max(value)

  from #test

  group by id

  but if the desire result is exactly as what you have shown,do the following snippet :

  select T.*,A.maxval

  from

   #test T

  inner join

   (select id,max(value)

   from #test

   group by id) A(id,maxval)

    on A.id=T.id