Top n with a twist

Question

Post reply

Top n with a twist

Jeremy Kemp

SSCertifiable

Points: 6754
More actions
December 10, 2003 at 4:29 am

#84916

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

NPeeters SSChampion Points: 12358 More actions · Answer 1

NPeeters

SSChampion

Points: 12358

December 10, 2003 at 4:35 am

#485492

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)...

covitz Old Hand Points: 322 More actions · Answer 2

covitz

Old Hand

Points: 322

December 11, 2003 at 10:06 am

#485763

The keyword TOP with an ORDER BY will always return the TOP # only AFTER doing the ORDER BY.

noeld SSC Guru Points: 96590 More actions · Answer 3



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

cliffb SSCarpal Tunnel Points: 4547 More actions · Answer 4

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.

noeld SSC Guru Points: 96590 More actions · Answer 5

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):