Limit output to so many rows by like STATE. 1

[davism]

Hi all,

What is the best way to limit like the number of users per U.S. state to 5 or less?

Let's say that I have a table where I have like:

User State
---------------
test1 AL
test2 AL
test3 CO
test4 AL
test5 CO
test6 CO
test7 CO
test8 AL
test10 CO
test11 CO
test12 AL
test13 AK
test14 AK
test15 AK

I want a result set of like:

test1 AL
test2 AL
test4 AL
test8 AL
test12 AL
test13 AK
test14 AK
test15 AK
test3 CO
test5 CO
test6 CO
test7 CO
test10 CO

What would be a query to do something like that? Note it doesn't include ALL but it includes 5 or less broken by state. I would imagine there would be a GROUP BY but I can't get something to work.

Any assistance would be greatly appreciated.

Thanks

 

[gmmastros]

Nope. Not a group by. You can use the Row_Number function as long as you are using SQL2005 or newer, like this...

Select  [user], State
From    (
          Select [user],state,Row_Number() Over (Partition By State Order By [User]) As RowId
          From    [!]YourTableName[/!]
        ) As A
Where   RowId <= 5

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

But what if I'm not using SQL Server 2005 but rather SQL Server 2000. Don't ask why and I'm not happy about it but do. :-(

 

[gmmastros]

Well then... it get's a little ugly. It's still do-able, but ugly.

The basic premise here is that we build a temp table or table variable that has the data sorted by state and then by user. This table variable would have an identity column. When then get the min value of the identity column for each state and use this to limit the rows.

The following query demonstrates this. You can copy/paste the entire code below in to a query window and run it. The @Temp table would represent your real table. Once you are satisfied that this works, you would remove the @Temp table stuff and change the 'insert into @Data' query to use your real table instead.

Declare @Temp Table([User] VarChar(30), State VarChar(2))

Insert Into @Temp Values('test1' ,   'AL')
Insert Into @Temp Values('test2' ,   'AL')
Insert Into @Temp Values('test3' ,   'CO')
Insert Into @Temp Values('test4' ,   'AL')
Insert Into @Temp Values('test5' ,   'CO')
Insert Into @Temp Values('test6' ,   'CO')
Insert Into @Temp Values('test7' ,   'CO')
Insert Into @Temp Values('test8' ,   'AL')
Insert Into @Temp Values('test10',   'CO')
Insert Into @Temp Values('test11',   'CO')
Insert Into @Temp Values('test12',   'AL')
Insert Into @Temp Values('test13',   'AK')
Insert Into @Temp Values('test14',   'AK')
Insert Into @Temp Values('test15',   'AK')

Declare @Data Table(RowId Int Identity(1,1), UserName VarChar(100), State VarChar(100))

Insert Into @Data(UserName, State)
Select [User], State
From   @Temp
Order By State, [User]

Select	*, RowId - MinRow
From    @Data D
        Inner Join (
          Select State, Min(RowId) As MinRow 
          From   @Data
          Group BY State
          ) As MaxData
          On D.State = MaxData.State
Where   RowId - MinRow < 5

Note the "< 5" part. Since we are doing a straight subtraction, the first row would have RowId = 1 and MinRowId = 1 so the subtraction would be 0. To get just 5 items, we will actually be returning items where RowId - MinRow between 0 and 4.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom