Need help with Query... - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with Query...

Author

Topic

Sanatan
Starting Member

19 Posts

Posted - 2008-04-03 : 19:39:04

I have a table of following type data...
Col1 Col2 Col3
111 1 xxx
111 2 yyy
111 3 zzz
222 1 abc
222 2 def
333 0 aaa
333 1 bbb

I need to write an query tp extract all data rows with the max(Col2) value for corresponsing Col1 e.g. query should retrieve the following rows in this case...

111 3 zzz
222 2 def
333 1 bbb

Can someone please assist with this??? I need this a bit quick, any assistance is appreciated. Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-04-03 : 19:56:34

>>I need this a bit quick
Are you taking an exam?


select col1, col2, col3
from    (
	select col1, col2, col3, row_number() over (partion by col1 order by col2 desc) as rn
	) d
where	rn = 1

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-03 : 19:59:07

I'd handle it like this:

SELECT Col1, Col2, Col3
FROM YourTable t
INNER JOIN (SELECT Col1, MAX(Col2) AS Col2 FROM YourTable GROUP BY Col1) d
ON t.Col1 = d.Col1 AND t.Col2 = d.Col2

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-04-03 : 20:41:02

That's the one. I almost got it right here: http://www.dbforums.com/showthread.php?t=1628986

Sanatan
Starting Member

19 Posts

Posted - 2008-04-03 : 20:53:06

Thank you folks... I really appreciate. Have a good night!

raky
Aged Yak Warrior

767 Posts

Posted - 2008-04-04 : 07:13:34

SELECT
T1.COL1,T1.COL2,T1.COL3
FROM
TABLENAME T1
WHERE
T1.COL2 = ( SELECT TOP 1 T2.COL2 FROM TABLENAME T2 WHERE T1.COL1= T2.COL1 ORDER BY T2.COL2 DESC)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources