Help please! Selecting Top Item by Group

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

Help please! Selecting Top Item by Group

Author

Topic

karenin
Starting Member

1 Post

Posted - 2011-06-30 : 14:15:02

Hi,

I am trying to capture the minimum values of a distribution by group. For example, I want to identify the stores with the lowest sales by region from the following table:
Region Store Sales
South 1000 3000
South 1001 4000
East 1002 2000
East 1003 1000
North 1004 6000
North 1005 4000

That is, I want my query to return:
Region Store Sales
South 1000 3000
East 1003 1000
North 1005 4000

How can I do this?

The following query will give me lowest sales by region, but I need to know which store is implicated:
Select Region, min(Sales)
From [Table]
Group by Region;

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-30 : 14:23:29

select Region,Store,Sales from
(select row_numbwer() over (partition by Region order by Sales ASC) as rownum,* from YourTable)dt
where rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-05 : 13:34:51

or simply this if you're on version before 2005


select t.*
from table t
inner join (select region,min(Sales) as minsales
            from table
            group by region)t1
on t1.region = t.region
and t1.minsales = t.Sales

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-05 : 14:07:35

lol - this is getting close to spam :).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-05 : 14:16:08

Didnt understand how that could be spam?
I just gave a suggestion which works for older versions just in case OP is using earlier version. Windowing functions are available only from SQL 2005 onwards.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources