Top N from each group - 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

General SQL Server Forums

New to SQL Server Programming

Top N from each group

Author

Topic

gabriel.k
Starting Member

6 Posts

Posted - 2007-03-15 : 15:26:57

Hi, I have a simple table of Cities like this

IdCity | City | State |

and i'm trying to retrieve n cities (3 in this case) from each state, like this


IdCity | City | State |
   1     nono    CA
   2     nono    CA
   3     nono    CA
  45     nono    MI
  46     nono    MI
  47     nono    MI

and so on.
How can I do that?

Thanks in advance,
Gabriel

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-03-15 : 15:32:32

what are you trying to do with the information?

The Yak Village Idiot

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-03-15 : 15:39:47

One thing you could try is this:

Declcare @TempTable table (IdCity [type], City [type], State [type])
Declare @countvariable
Set @countvariable = null
select top 1 @countvariable = state from [table_name] group by state order by state
while @countvariable is not null
begin
insert into @temptable(idcity, city, state)
select top 3 idcity, city, state from [table_name] where state = @countervariable order by [criteria]
select @countervariable = state from [table_name] where state > @countervariable order by state
if @@rowcount = 0 begin set @countervariable = null end
end
select * from @temptable

This loop will return the top three records from each state by some criteria and inserts them into a temporary table and then the last select returns all of the information stored in the temporary table.

This may be more than you want but I hope it helps. Just copy and past into your query and replace the [] with the real data.

The Yak Village Idiot

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-15 : 15:59:22

Here is one way:

DECLARE @City TABLE(ID int, City VARCHAR(50), State VARCHAR(2))

INSERT @City
SELECT 1, 'nono', 'CA' UNION ALL
SELECT 2, 'nono', 'CA' UNION ALL
SELECT 3, 'nono', 'CA' UNION ALL
SELECT 45, 'nono', 'MI' UNION ALL
SELECT 46, 'nono', 'MI' UNION ALL
SELECT 47, 'nono', 'MI' 

SELECT 
	a.ID, 
	a.City, 
	a.State
FROM 
	@City a
INNER JOIN
	@City b
	ON a.State = b.State
WHERE 
	a.ID >= b.ID
GROUP BY 
	a.ID, 
	a.City, 
	a.State
HAVING 
	COUNT(*) BETWEEN 1 AND 3

-Ryan

gabriel.k
Starting Member

6 Posts

Posted - 2007-03-15 : 16:04:09

Thanks... this is for a new table i have to populate with sample data to test, so i need to get a few cities from each state.

I'll try your suggestion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-15 : 16:16:45

Here are two SQL 2005 ways. (I wasn't sure what version you are using).

DECLARE @City TABLE(ID int, City VARCHAR(50), State VARCHAR(2))

INSERT @City
SELECT 1, 'nono', 'CA' UNION ALL
SELECT 2, 'nono', 'CA' UNION ALL
SELECT 3, 'nono', 'CA' UNION ALL
SELECT 45, 'nono', 'MI' UNION ALL
SELECT 46, 'nono', 'MI' UNION ALL
SELECT 47, 'nono', 'MI'; 

-- Method 1
WITH Cities AS
(
	SELECT ID, City, State,
	ROW_NUMBER() OVER (PARTITION BY State ORDER BY State) AS RowNumber
	FROM @City 
) 
SELECT * 
FROM Cities 
WHERE RowNumber between 1 and 3;
--Method 2
SELECT 
	a.ID, a.City, a.State
FROM 
	(
		SELECT ID, City, State, ROW_NUMBER() OVER (PARTITION BY State ORDER BY State) AS Temp
		FROM @City
	) a
INNER JOIN 
@City b
	ON b.ID = a.ID
WHERE 
	Temp BETWEEN 1 AND 3

Cheers,

-Ryan

gabriel.k
Starting Member

6 Posts

Posted - 2007-03-15 : 16:37:06

I forgot to say it's SQL 2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources