Select value where max(other colum)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select value where max(other colum)

Author

Topic

ChristopheB
Starting Member

3 Posts

Posted - 2008-09-17 : 08:34:24

Table:
COL1 Col2 Col3
1 20 A
1 15 B
1 30 C
2 20 D
2 15 E
3 30 F
3 60 G
3 45 H

How can I get the following result
1 C
2 D
3 G

So the letter(col3) next to the highest value of column2 for each group(Col1).

It's driving me crazy.
thank you,
christophe

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 08:37:07

[code]SELECT t1.Col1,t1.COL3
FROM Yourtable t1
INNER JOIn (SELECT Col1,MAX(col2)as max
FROm Yourtable
GROUP BY col1)tmp
on tmp.col1=t1.col1
and tmp.max=t1.col2[/code]

ChristopheB
Starting Member

3 Posts

Posted - 2008-09-17 : 09:10:19

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources