Use ROWNUM with GROUP BY - ARRGH!

Help....

I am trying to use ROWNUM to return the first 20 records of an ordered list. I am using a subquery to group records and return a COUNT(*) which I then want to see the top 20 of. For example:

SELECT COUNT(*), ARTIST, TITLE FROM RECORDS_SOLD GROUP BY ARTIST,TITLE SORT BY COUNT(*) DESC would return a count of all records sorted from most to least popular.

I have had to resort to using a subquery to return the records in order to use ROWNUM, otherwise each record in the GROUP BY gets it's own ROWNUM and of course all records return a count of 1. For example:

SELECT * FROM (
SELECT COUNT(*), ARTIST, TITLE FROM RECORDS_SOLD GROUP BY ARTIST,TITLE )
WHERE ROWNUM < 21 SORT BY COUNT(*) DESC

Unfortunately, you can't use ORDER BY in a subquery, so instead of getting the top 20 I get the first 20 records returned by Oracle in "whatever" order. Pretty useless.

So, apparently I can either have the whole thing (sorted) or a random 20 records.

Does anybody know a way to return the top "X" records from a sorted list???????

Thanks - Tom Received on Fri Nov 19 1999 - 18:11:35 CST