Top n with a twist
-
I have a table which has a product code column, a time period column and a count column (and several others but they are not important for this).
What I am trying to get is a a list of the top n products (ranked by decreasing count)
for each time period.
If I use:
select top 10 product, time_period
from <table>
order by <count> desc
it only gives me 10 rows whereas I want 10 rows for each time period - if I have 5 different time periods I want 50 rows. I know that I can do this by determining the unique values of time periods and and loop round for each value (and put in a where time_period = statement in the above SQL) but I wanted to know if there was another/better way of doing it.
Ideas welcome.
Jeremy
-
Try the following code
SELECT t1.product, t1.time_period
FROM <table> t1
WHERE t1.unique_row_id IN
(SELECT TOP 10 t2.unique_row_id
FROM <table> t2
WHERE t2.time_period = t1.time_period)Not the best for performance, but it will do the trick (I think)...
-
The keyword TOP with an ORDER BY will always return the TOP # only AFTER doing the ORDER BY.
-
CREATE TABLE TBL (GRP INT, VALUE INT)
GO
INSERT TBL VALUES (1, 1)
INSERT TBL VALUES (1, 2)
INSERT TBL VALUES (1, 3)
INSERT TBL VALUES (1, 4)
INSERT TBL VALUES (1, 5)
INSERT TBL VALUES (2, 2)
INSERT TBL VALUES (2, 3)
INSERT TBL VALUES (2, 5)
INSERT TBL VALUES (2, 6)
INSERT TBL VALUES (3, 1)
INSERT TBL VALUES (3, 2)
INSERT TBL VALUES (3, 3)
INSERT TBL VALUES (4, 8)
INSERT TBL VALUES (4, 3)
INSERT TBL VALUES (4, 5)
INSERT TBL VALUES (4, 1)
INSERT TBL VALUES (4, 9)
GO
DECALRE @TOPX INT
SET @TOPX = 3
SELECT
T1.GRP
, T1.VALUE
, COUNT(*) AS RANK
FROM
TBL T1
JOIN
TBL T2
ON
T1.GRP = T2.GRP AND
T1.VALUE <= T2.VALUE
GROUP BY
T1.GRP, T1.VALUE
HAVING
COUNT(*) <= @TOPX
ORDER BY
T1.GRP,COUNT(*),T1.VALUE
Again, I can't stressed enough, be aware of
possible Duplicates
HTH
* Noel -
quote:
CREATE TABLE TBL (GRP INT, VALUE INT)
GO
INSERT TBL VALUES (1, 1)
INSERT TBL VALUES (1, 2)
INSERT TBL VALUES (1, 3)
INSERT TBL VALUES (1, 4)
INSERT TBL VALUES (1, 5)
INSERT TBL VALUES (2, 2)
INSERT TBL VALUES (2, 3)
INSERT TBL VALUES (2, 5)
INSERT TBL VALUES (2, 6)
INSERT TBL VALUES (3, 1)
INSERT TBL VALUES (3, 2)
INSERT TBL VALUES (3, 3)
INSERT TBL VALUES (4, 8)
INSERT TBL VALUES (4, 3)
INSERT TBL VALUES (4, 5)
INSERT TBL VALUES (4, 1)
INSERT TBL VALUES (4, 9)
GO
DECALRE @TOPX INT
SET @TOPX = 3
SELECT
T1.GRP
, T1.VALUE
, COUNT(*) AS RANK
FROM
TBL T1
JOIN
TBL T2
ON
T1.GRP = T2.GRP AND
T1.VALUE <= T2.VALUE
GROUP BY
T1.GRP, T1.VALUE
HAVING
COUNT(*) <= @TOPX
ORDER BY
T1.GRP,COUNT(*),T1.VALUE
Again, I can't stressed enough, be aware of
possible Duplicates
HTH
I don't beleive this will return what the OP is looking for. Your query would return a set of results where the number of records was greater than 3 for the result set. What the OP is looking for is a list something like this:
Product,Time Frame
-----------------------
eye of the world,dec10
the dragon reborn,dec10
interview with a vampire,dec10
lord of chaos,dec10
books of blood,dec10
the years best science fiction,dec10
teach yourself sql in 24 hours,dec10
professional C#,dec10
when true night falls,dec10
rusalka,dec10
eye of the world,dec12
the dragon reborn,dec12
interview with a vampire,dec12
lord of chaos,dec12
books of blood,dec12
the years best science fiction,dec12
teach yourself sql in 24 hours,dec12
professional C#,dec12
when true night falls,dec12
rusalka,dec12
IE, what are the top 10 products for each time period specified. I can using your query suggestion to gather the top x in a time period, but my suggestion would be to use a cursor, implement your sql for each loop.
I have a similar issue I am working on but have not found anyway without using a cursor as of yet. I'll be watching this post to see if a new suggestion creeps up that I might be able to apply.
-
quote:
quote:
CREATE TABLE TBL (GRP INT, VALUE INT)
GO
INSERT TBL VALUES (1, 1)
INSERT TBL VALUES (1, 2)
INSERT TBL VALUES (1, 3)
INSERT TBL VALUES (1, 4)
INSERT TBL VALUES (1, 5)
INSERT TBL VALUES (2, 2)
INSERT TBL VALUES (2, 3)
INSERT TBL VALUES (2, 5)
INSERT TBL VALUES (2, 6)
INSERT TBL VALUES (3, 1)
INSERT TBL VALUES (3, 2)
INSERT TBL VALUES (3, 3)
INSERT TBL VALUES (4, 8)
INSERT TBL VALUES (4, 3)
INSERT TBL VALUES (4, 5)
INSERT TBL VALUES (4, 1)
INSERT TBL VALUES (4, 9)
GO
DECLARE @TOPX INT
SET @TOPX = 3
SELECT
T1.GRP
, T1.VALUE
, COUNT(*) AS RANK
FROM
TBL T1
JOIN
TBL T2
ON
T1.GRP = T2.GRP AND
T1.VALUE <= T2.VALUE
GROUP BY
T1.GRP, T1.VALUE
HAVING
COUNT(*) <= @TOPX
ORDER BY
T1.GRP,COUNT(*),T1.VALUE
Again, I can't stressed enough, be aware of
possible Duplicates
HTH
I don't beleive this will return what the OP is looking for. Your query would return a set of results where the number of records was greater than 3 for the result set. What the OP is looking for is a list something like this:
Product,Time Frame
-----------------------
eye of the world,dec10
the dragon reborn,dec10
interview with a vampire,dec10
lord of chaos,dec10
books of blood,dec10
the years best science fiction,dec10
teach yourself sql in 24 hours,dec10
professional C#,dec10
when true night falls,dec10
rusalka,dec10
eye of the world,dec12
the dragon reborn,dec12
interview with a vampire,dec12
lord of chaos,dec12
books of blood,dec12
the years best science fiction,dec12
teach yourself sql in 24 hours,dec12
professional C#,dec12
when true night falls,dec12
rusalka,dec12
IE, what are the top 10 products for each time period specified. I can using your query suggestion to gather the top x in a time period, but my suggestion would be to use a cursor, implement your sql for each loop.
I have a similar issue I am working on but have not found anyway without using a cursor as of yet. I'll be watching this post to see if a new suggestion creeps up that I might be able to apply.
Did you tested?
That Count(*) represent the ranked value within each group
Result of the above query (for TopX = 3):
GRP VALUE RANK
----------- ----------- -----------
1 5 1
1 4 2
1 3 3
2 6 1
2 5 2
2 3 3
3 3 1
3 2 2
3 1 3
4 9 1
4 8 2
4 5 3
Result of the above query (for TopX = 2):
GRP VALUE RANK
----------- ----------- -----------
1 5 1
1 4 2
2 6 1
2 5 2
3 3 1
3 2 2
4 9 1
4 8 2
Isn't that what was asked?
* Noel
Isn't that what was asked?Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply