Access Top Row in Group – SQL Query

Sometimes when we are dealing with tables with a huge amount of records in database, we want to get the first a few records of each group of data, how do we do that? For example, we have data like this:

| Category | Item             | Count |
|---------:|------------------|-------|
| Cat      | Bengal           | 3     |
| Cat      | Birman           | 2     |
| Cat      | Abyssinian       | 9     |
| Dog      | Jack Russell     | 7     |
| Dog      | Beagle           | 2     |
| Dog      | Golden Retriever | 1     |
| Dog      | Shih Tzu         | 4     |

and we need:

| Category | Item         | Count |
|---------:|--------------|-------|
| Cat      | Abyssinian   | 9     |
| Cat      | Bengal       | 3     |
| Dog      | Jack Russell | 7     |
| Dog      | Shih Tzu     | 4     |

The following code is the perfect solution. If you want to add dimension of the filtering conditions, the only thing you need to do is to increase the criteria under the Group By clause (as I added "year" for example). The INNER JOIN clause is used here to avoid possible external interference.

SELECT a.item,
a.category,
a.year,
a.inventorycount,
COUNT(*) AS ranknumber
FROM inv AS a
INNER JOIN inv AS b
ON (a.category = b.category)
AND (a.year = b.year)
AND (a.inventorycount <= b.inventorycount)GROUP BY a.category, a.item,
a.year, a.inventorycount
HAVING COUNT(*) <= 2ORDER BY a.year, a.category, COUNT(*) DESC