Max across grouping - 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)

Max across grouping

Author

Topic

pravin14u
Posting Yak Master

246 Posts

Posted - 2010-03-07 : 01:57:28

I have the following columns in my table:

(Market,
Region,
Product,
Volume)

I want to get the list of "Product" which has max(volume) across Market,Region groups.

Thanks!

Prakash.P
The secret to creativity is knowing how to hide your sources!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-07 : 03:05:17

[code]
select *
from
(
select *, row_no = row_number() over(partition by Market, Region order by volume desc)
from mytable
) a
where a.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 08:29:29

also


SELECT t.Market,
t.Region,
t.Product,
t.Volume
FROM table t
CROSS APPLY (SELECT TOP 1 volume 
             FROM table
             WHERE Market = t. Market
             AND Region = t.Region
             ORDER BY volumne DESC)t1
WHERE t.volume = t1.volume

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources