how to group to get maxvalue and its zone? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

how to group to get maxvalue and its zone?

Author

Topic

sql4us
Starting Member

24 Posts

Posted - 2011-10-25 : 14:25:40

how to group by following columns and get the required output shown below?

name value zone
CA129R 2748 East
CA129R 543 west
CA129R 3000 north
CA129R 100 south
....
required output:

name value zone
CA129R 3000 north

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-25 : 14:50:41

Here's one way:


select name, value, zone
from   (
       select *
              ,rn = row_number() over (partition by name order by value desc) 
       from yourTable
       ) d
where rn = 1

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources