Group By Possible?

• Crispin Proctor

  SSCoach

  Points: 18161

  More actions

  December 9, 2003 at 8:57 am

  #107023

  Greetings.

  Consider the code below.

  Removing the COl1 from the select and group by returnes the desired results.

  ie: I want the distinct COl1 and MAX(Col2) values.

  When adding other data into the select, it does not work (as expected)

  Is there any way I can include Col1 into the results but still get distinct col1 and max col2?

  I am not concerned as to which col1 I get, but only want the one corresponding to the distinct + max combination.

  Create Table #xx(Col1Varchar(100), Col2Int, COl3Int)

  Insert Into #XX Values ('C', 1, 1)

  Insert Into #XX Values ('d', 1, 2) --Want

  Insert Into #XX Values ('g', 2, 1) --Want

  Insert Into #XX Values ('h', 3, 1)

  Insert Into #XX Values ('w', 3, 2) --Want

  Select Distinct Col1, Col2, Max(COl3) From #xx

  Group By COl2, Col1

  Go

  Drop Table #xx

  .

  Cheers,

  Crispin

  Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

• Keith Henry

  Hall of Fame

  Points: 3759

  More actions

  December 9, 2003 at 9:08 am

  #485354

  select x.* from

  #xx x

  inner join (

  Select Col2, Max(COl3) as 'mxCOl3' From #xx

  Group By Col2 ) g

  on x.Col2 = g.Col2 and x.COl3 = g.mxCOl3

  Keith Henry

  DBA/Developer/BI Manager

  
  
  
  Keith Henry
  

  ---

  
  According to everyone I know I "do something with computers?" for a living, so there you go.

• noeld

  SSC Guru

  Points: 96590

  More actions

  December 9, 2003 at 9:21 am

  #485355

  besides keith suggestion make sure Col2 with Col3 have no duplicates because then you may get ramdom Col1 values!!

  
  * Noel

• Crispin Proctor

  SSCoach

  Points: 18161

  More actions

  December 9, 2003 at 11:18 pm

  #485468

  Thanks!

  Cheers,

  Crispin

  Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!