Selecting the top 'x' of a range and reporting it

[matrun]

I'm after a way of getting a query in SQL (Access) to look at a range of classifications, and then taking the top 5 largest number of items [in this case] against that classification. For example; for a table holding data on products in a record store, you might want to ask it

"Tell me the top five bestsellers from each genre in the store"

This would mean the query would have to group by the genre, locate the stock count against each product, and return the top 5 for every genre it has grouped. Any ideas guys??
Thanks - Matt

 

[amorous]

show us some data and the kind of result you want.

-VJ

 

[matrun]

OK thanks - for example;

22 GOSPEL/CHRISTIAN VARESE SARABANDE
18 GOSPEL/CHRISTIAN WORD
17 GOSPEL/CHRISTIAN ACE
16 GOSPEL/CHRISTIAN EMI
15 GOSPEL/CHRISTIAN REBEL
15 GOSPEL/CHRISTIAN SHANACHIE
14 GOSPEL/CHRISTIAN ACE
14 GOSPEL/CHRISTIAN COLLEGIUM
13 GOSPEL/CHRISTIAN COMPENDIA
12 GOSPEL/CHRISTIAN TOMMY BOY

203 JAZZ WARNER JAZZ
196 JAZZ BLACK & BLUE
186 JAZZ POLYGRAM
183 JAZZ 32 JAZZ
176 JAZZ FRESH SOUND
174 JAZZ DELMARK
171 JAZZ BLACK SAINT
169 JAZZ CANDID
166 JAZZ ORIGINAL JAZZ CLASSICS
165 JAZZ KNITTING FACTORY

186 R&B/SOUL ZYX
158 R&B/SOUL ACE
139 R&B/SOUL IMS
131 R&B/SOUL CHARLY
113 R&B/SOUL UNIVERSAL
101 R&B/SOUL SEQUEL
101 R&B/SOUL STAX
99 R&B/SOUL GOLDMINE SOUL SUPPLY
92 R&B/SOUL MOTOWN
86 R&B/SOUL IMS
84 R&B/SOUL SPECTRUM
76 R&B/SOUL EMI

Would return:

22 GOSPEL/CHRISTIAN VARESE SARABANDE
18 GOSPEL/CHRISTIAN WORD
17 GOSPEL/CHRISTIAN ACE
16 GOSPEL/CHRISTIAN EMI
15 GOSPEL/CHRISTIAN REBEL

203 JAZZ WARNER JAZZ
196 JAZZ BLACK & BLUE
186 JAZZ POLYGRAM
183 JAZZ 32 JAZZ
176 JAZZ FRESH SOUND

186 R&B/SOUL ZYX
158 R&B/SOUL ACE
139 R&B/SOUL IMS
131 R&B/SOUL CHARLY
113 R&B/SOUL UNIVERSAL

Thanks!

 

[amorous]

Try something like this:


SELECT genreid, genre, genretype
FROM tablename t
WHERE genreid in (
select TOP 5 genreid
from tablename
where genreid = t.genreid)


you have to use your own table names and fields.


Hope this helps

VJ

 

[matrun]

Hi - that is really helpful! thanks... It seems though to be selectively returning 4 or 3 lines each time, without being the top ones. So, against classical, I had results returning with 50, 26, 7 & 3, (only 4), even though the highest value was 1693.

Why do you think it's grouping like that?

M

 

[amorous]

Does this work:

SELECT TOP 5 genreid, genre, genretype
FROM tablename t
WHERE genreid in (
select genreid
from tablename
where genreid = t.genreid)

-VJ

 

[amorous]

And what data type is your id field??


-VJ

 

[matrun]

Hi - it's long integer (typical Access!). I've seen what's going on; it's taking the ID bit, and saying, what is the highest ID value that all genres have in common, which is 50, and then doing the next in line. So it's looking at the ID first globally, finding the highest one common to all, and then doing the query. What it needs to do is look at the genre, then WITHIN the genre find the 5 highest IDs, and then move on to the next. This might be a VB thing, I'm wondering, or, do you think it's doable in SQL?

Matt

 

[lespaul]

Don't you need to order by genreid in the subquery in order to return the correct top 5?

leslie

SELECT genreid, genre, genretype
  FROM tablename t
 WHERE genreid in (
     select TOP 5 genreid
       from tablename
      where genreid = t.genreid order by genreid desc)

Leslie

 

[amorous]

Yes leslie,

I missed that order by part..

Matt try the query provided by leslie. It should work.

-VJ

 

[matrun]

Hi - that either returns a) everything if the order by is on genre_desc or, b) if it's by ID, then it just lists all the records with an ID value if 1!

I tried

SELECT CountOfSTOCK_PRODUCT_ID, GENRE_DESC, LABEL_DESC
FROM Distr_Label_Genre
WHERE CountOfSTOCK_PRODUCT_ID in (
select TOP 5 CountOfSTOCK_PRODUCT_ID
from Distr_Label_Genre
where CountOfSTOCK_PRODUCT_ID = Distr_Label_Genre.CountOfSTOCK_PRODUCT_ID order by CountOfSTOCK_PRODUCT_ID);

and

SELECT CountOfSTOCK_PRODUCT_ID, GENRE_DESC, LABEL_DESC
FROM Distr_Label_Genre
WHERE CountOfSTOCK_PRODUCT_ID in (
select TOP 5 CountOfSTOCK_PRODUCT_ID
from Distr_Label_Genre
where CountOfSTOCK_PRODUCT_ID = Distr_Label_Genre.CountOfSTOCK_PRODUCT_ID order by genre_desc);

 

[lespaul]

try this:


SELECT CountOfSTOCK_PRODUCT_ID, GENRE_DESC, LABEL_DESC
FROM Distr_Label_Genre
WHERE CountOfSTOCK_PRODUCT_ID in (
select TOP 5 CountOfSTOCK_PRODUCT_ID
from Distr_Label_Genre
where CountOfSTOCK_GENRE_DESC = Distr_Label_Genre.GENRE_DESC order by CountOfSTOCK_PRODUCT_ID);





Leslie

 

[amorous]

Your query shud look like something:

SELECT genreid, genre, genretype
FROM tempdb.dbo.genretable t
WHERE genreid in (
select TOP 5 genreid
from tempdb.dbo.genretable
where genre = t.genre
order by genreid desc)

-VJ

 

[amorous]

oops i am slow..

-VJ

 

[matrun]

Oh dear guys, I'm afraid I tried yours Leslie, and it returns all records with an ID = 1!!!! Argh! What to do?

 

[amorous]

Did u try this exactly:

SELECT CountOfSTOCK_PRODUCT_ID, GENRE_DESC, LABEL_DESC
FROM Distr_Label_Genre
WHERE CountOfSTOCK_PRODUCT_ID in (
select TOP 5 CountOfSTOCK_PRODUCT_ID
from Distr_Label_Genre
where CountOfSTOCK_GENRE_DESC = Distr_Label_Genre.GENRE_DESC order by CountOfSTOCK_PRODUCT_ID DESC);


-VJ

 

[matrun]

Interesting! Well, if I try that exactly, it prompts for a value for CountOfSTOCK_GENRE_DESC, but that's because I don't have a field called that - it's called just genre_desc. If I remove that, and keep the DESC in at the end, it gives me this:

CountOfSTOCK_PRODUCT_ID GENRE_DESC LABEL_DESC
1852 CLASSICAL DECCA
2707 CLASSICAL EMI CLASSICS
2365 CLASSICAL NAXOS
1732 FEATURE FILM CINEMA CLUB
1869 ROCK/POP EMI

...which is certainly better in a way, but it seems like it's still not selecting by genre, then finding the top five by ID within that genre, then moving on to the next genre and doing the same... Does this make sense?

 

[amorous]

Then it should be

SELECT CountOfSTOCK_PRODUCT_ID, GENRE_DESC, LABEL_DESC
FROM Distr_Label_Genre
WHERE CountOfSTOCK_PRODUCT_ID in (
select TOP 5 CountOfSTOCK_PRODUCT_ID
from Distr_Label_Genre
where GENRE_DESC = Distr_Label_Genre.GENRE_DESC order by CountOfSTOCK_PRODUCT_ID DESC);

-VJ

 

[lespaul]

OK, we would really like to help.

You have ONE table right? it's called Dist_Label_Genre (or is this a query?)

your table or query has the fields:

CountOfSTOCK_PRODUCT_ID
GENRE_DESC
LABEL_DESC

You want to select the top 5 countofStock_Product_ID for EACH genre_desc.

SELECT D.COUNTOFSTOCK_PRODUCT_ID, D.GENRE_DESC, D.LABEL_DESC
FROM DIST_LABEL_GENRE
WHERE D.COUNTOFSTOCK_PRODUCT_ID IN (SELECT TOP 5 D2.COUNTOFSTOCK_PRODUCT_ID FROM DIST_LABEL_GENRE D2 WHERE D2.GENRE_DESC = D.GENRE_DESC ORDER BY D2.COUNTOFSTOCK_PRODUCT_ID)

should work.

leslie

 

[matrun]

Hi Leslie

Yeah - it's actually DISTR_LABEL_GENRE. You're right about the other. When I paste that in, it doesn't recognize the d. prefix of D.COUNTOFSTOCK_PRODUCT_ID and so on (is prompting for a value). I assume d. is an alias?