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

 

[matrun]

Hi - just to go back to you earlier there, VJ; I did what you put up there, but that's when I got this grouping:

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

 

[amorous]

SELECT D.COUNTOFSTOCK_PRODUCT_ID, D.GENRE_DESC, D.LABEL_DESC
FROM DIST_LABEL_GENRE D
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)

leslie missed that part
-VJ

 

[matrun]

Oh guys - I'm afraid it still returns a maximum value ID = 1. I feel bad asking the question now. I think the problem is that it needs to be done in terms of routines - select genre, find the top 5 IDs, then repeat for next genre on the list. The problem is that bit of code isn't able to indicate the repeat element, so the groupings always go wrong.
I think I'll have to see if it can be done in VB or PL-SQL or something.

Thanks so much for all your help; I suppose at least it's given you a work-out!!!

M

 

[lespaul]

what about this instead:


SELECT D.COUNTOFSTOCK_PRODUCT_ID, D.GENRE_DESC, D.LABEL_DESC
FROM DIST_LABEL_GENRE D
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) AND D.GENRE_DESC = D2.GENRE_DESC


Leslie