Select TOP 2 Quanities Per ID
-
Hi,
I am needing a little help.
I need to return two rows per ID from one table which are the top2 quantities.
Here is the code to create a test table and data.
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'MyTable' and xType = 'U')
DROP TABLE MyTable
CREATE Table MyTable (
ID int,
Quantity int)
INSERT MyTable VALUES (1, 1000)
INSERT MyTable VALUES (1, 500)
INSERT MyTable VALUES (1, 600)
INSERT MyTable VALUES (1, 3000)
INSERT MyTable VALUES (2, 100)
INSERT MyTable VALUES (2, 300)
INSERT MyTable VALUES (2, 2000)
INSERT MyTable VALUES (2, 10000)
INSERT MyTable VALUES (3, 200)
INSERT MyTable VALUES (3, 7000)
INSERT MyTable VALUES (3, 6000)
From this I would like to get the results
ID Quantity
--------- -----------
1 3000
1 1000
2 10000
2 2000
3 7000
3 6000
I have been searching the forums as I know I have seen a solution to a similar type problem before but so far I am stumped.
If you have the time I also need to get the result set back in the format.
ID Quantity1 Quantity2
---------- ---------- ---------
1 3000 1000
2 10000 2000
3 7000 6000
Thanks in advance.
-
This will get you the top 2 for each id:-
WITH cte AS (SELECT id, quantity, ROW_NUMBER() OVER(partition by Id ORDER BY Id, quantity desc) AS 'RowNumber' from mytable)select id,quantity from cte where rownumber < 3
If you want to concatenate the output, have a look at this article for various methods
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
-
Hi,
Thanks for the reply but I am using SQL Server 2000 so does not work.
I am getting an Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
Server: Msg 195, Level 15, State 1, Line 1
'ROW_NUMBER' is not a recognized function name.
-
Russell.Taylor (7/7/2009)
Thanks for the reply but I am using SQL Server 2000 so does not work.Doh!!
Sorry, ROW_NUMBER isn't supported in SQL 2000.
There is a solution using a self join (join the table to "select top 2" from the same table), which I can't test as I'm away from the office for a few days.
There's probably a neater solution using a tally table... if Jeff Moden sees this post, he'll probably be able to supply you with a query which will scale much better.
-
SELECT a.ID,
MAX(a.Quantity) AS Quantity1,
MAX(b.Quantity) AS Quantity2
FROM MyTable a
LEFT OUTER JOIN MyTable b ON b.ID=a.ID AND b.Quantity<a.Quantity
GROUP BY a.ID
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark thank you for your response but the query you supplied does not work on syntax,
Have changed it to:
SELECT a.ID,
MAX(a.Quantity) AS Quantity1,
MAX(b.Quantity) AS Quantity2
FROM MyTable a
LEFT OUTER JOIN MyTable b ON b.ID=a.ID AND b.Quantity = a.quantity
GROUP BY a.ID,a.quantity
But this then returns the same quantity for both columns.
If I change the second selected column to MIN this returns the wrong value as it returns the lowest quantity for the ID and not the second highest.
-
Russell.Taylor (7/8/2009)
Mark thank you for your response but the query you supplied does not work on syntax,What syntax error are you getting?
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Just relaised its a problem with the text displayed in Google Chrome so when copied to Query Analyser it has html tags in it, copied it again in IE7 and its OK. Sorry about that, getting the results I was looking for now.
Many Thanks
R
MCITP SQL 2005, MCSA SQL 2012
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply