Query for taking Top # per category

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 2000 Forums

Transact-SQL (2000)

Query for taking Top # per category

Author

Topic

srowan
Starting Member

4 Posts

Posted - 2004-08-25 : 10:38:03

The data I'm really working with is a lot more complicated than can be easily put in here (and it's kind of sensitive), so I'll ask this using a hypothetical example.

Suppose I have a table containing information on cars and their prices that looks like this:
[index] [manufacturer] [model] [price]

Suppose the table contains data for Mazda, Dodge, Honda, and Ford (in the [manufacturer] attribute of course), each with approximately 10 records for different models they make and corresponding prices.

What I would like to do is write a query that will grab the top 5 most expensive cars for each manufacturer. What would that query look like?

Thanks in advance.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-25 : 10:56:35

Here's an example...


Declare @myTable table (cat varchar(100), subcat varchar(100), rank int )
Insert Into @myTable 
Select 'A', 'a', 2 
Union All Select 'A', 'b', 52 
Union All Select 'A', 'c', 14 
Union All Select 'A', 'd', 46
Union All Select 'A', 'e', 37 
Union All Select 'A', 'f', 95 
Union All Select 'A', 'g', 73 
Union All Select 'A', 'h', 67 
Union All Select 'A', 'i', 80 
Union All Select 'A', 'j', 03 
Union All Select 'B', 'a', 18 
Union All Select 'B', 'b', 44 
Union All Select 'B', 'c', 52 
Union All Select 'B', 'd', 60 
Union All Select 'B', 'e', 28 
Union All Select 'B', 'f', 06 
Union All Select 'B', 'g', 70 
Union All Select 'B', 'h', 90 
Union All Select 'B', 'i', 89 
Union All Select 'B', 'j', 31

--Select top n from each cat (n=5)
declare @n int
Set @n = 5

Select Cat, subCat, rank
From @myTable as A
Where (Select count(1) From @myTable Where cat=A.cat and rank <= A.rank)<=@n
Order By Cat, Rank Desc

Corey

srowan
Starting Member

4 Posts

Posted - 2004-08-25 : 17:40:41

Sorry, Seventhnight, I tried your solution but I think it caused my query to time out (it ran for 6 minutes and then spit back a very broken result set). I am dealing with around 110,000 records though. In case anyone is interested though, I did find another solution:

--------------------
CREATE TABLE #temp --temp table to store your result
(<insert attributes here>)

DECLARE @index <type>

DECLARE cursor_name CURSOR
FOR
SELECT index FROM lookup_table

OPEN cursor_name

FETCH NEXT FROM lookup_table INTO @index

WHILE @@FETCH_STATUS = 0 --while there are still records available
BEGIN
INSERT INTO #temp (<attribute list>)
SELECT TOP 5 FROM <the table you are trying to query> AS a
WHERE a.index = @index
FETCH NEXT FROM lookup_table INTO @index
END

CLOSE cursor_name
DEALLOCATE cursor_name
--------------------
Sorry, if that's a little vague for the variable names and such. I could probably come up with a better example if anyone is interested.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-25 : 21:03:41

Hmm... it shouldn't have timed out or had a broken set. In general cursors are frowned upon around here, but if you got it to work and are satisfied that is what counts.

Sorry I wasn't more help

Corey

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-26 : 00:06:31

Why use a cursor. This will be faster than that.


DECLARE @SalesGouge TABLE(
	ident INT IDENTITY(1,1) PRIMARY KEY,
	manufacturer VARCHAR(55),
	model VARCHAR(55),
	price MONEY)

DECLARE @SalesRating TABLE(
	ident INT IDENTITY(1,1) PRIMARY KEY,
	manufacturer VARCHAR(55),
	price MONEY)

INSERT @SalesGouge(manufacturer, model, price)
	SELECT 'FORD','MUSTANG',$45000 UNION ALL
	SELECT 'FORD','MUSTANG GT',$55000 UNION ALL
	SELECT 'FORD','COUGAR',$48000 UNION ALL
	SELECT 'FORD','TAURUS',$43000 UNION ALL
	SELECT 'FORD','TEMPO',$20 UNION ALL
	SELECT 'FORD','EXPLORER',$65000 UNION ALL
	SELECT 'FORD','CONCORD',$54000 UNION ALL
	SELECT 'DODGE','INTREPID',$57000 UNION ALL
	SELECT 'DODGE','STRATIS',$49000 UNION ALL
	SELECT 'DODGE','NEON',$1 UNION ALL
	SELECT 'DODGE','STEALTH',$72000 UNION ALL
	SELECT 'DODGE','MINIVAN',$65000 UNION ALL
	SELECT 'DODGE','SUV',$85000 UNION ALL
	SELECT 'HONDA','ACCORD',$57000 UNION ALL
	SELECT 'HONDA','CIVIC',$36000 UNION ALL
	SELECT 'HONDA','PRELUDE',$38000 UNION ALL
	SELECT 'HONDA','ItWasMadeInJapanWhoKnowsWhatsInIt',$1000 UNION ALL
	SELECT 'HONDA','MUSTANGYeahRight',$58000 UNION ALL
	SELECT 'HONDA','CORVETTE',$80000 UNION ALL
	SELECT 'MAZDA','CAR1',$50000 UNION ALL
	SELECT 'MAZDA','CAR2',$20000 UNION ALL
	SELECT 'MAZDA','CAR3',$40000 UNION ALL
	SELECT 'MAZDA','CAR4',$60000 UNION ALL
	SELECT 'MAZDA','CAR5',$10000 UNION ALL
	SELECT 'MAZDA','CAR6',$30000

SELECT * FROM @SalesGouge

INSERT @SalesRating(manufacturer, price)
	SELECT manufacturer, price
	FROM @SalesGouge
	ORDER BY manufacturer, price DESC

SELECT
	sg.manufacturer,
	sg.model,
	sg.price
FROM
	@SalesGouge sg
	INNER JOIN @SalesRating sr1 ON sg.manufacturer = sr1.manufacturer
		AND sg.price = sr1.price
	INNER JOIN (
		SELECT manufacturer, MIN(ident) ident
		FROM @SalesRating 
		GROUP BY manufacturer) sr2 ON sr1.manufacturer = sr2.manufacturer
		AND sr1.ident <=sr2.ident + 4	
ORDER BY
	sg.manufacturer,
	sg.price DESC

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources