Access Top Row in Group – SQL Query

有时我们在处理数据库里 record 较多的 table 时，想筛选同时满足1或1个以上条件后按特定规则排序后的第一个数据，怎么做呢？

比如我们有如下内容，我们想输出 the highest Inventory of the top most 2 categories.

Category Item InventoryCount

------- ----- -------------

Beverage milk 3

Beverage water 2

Beverage beer 9

Utensil fork 7

Utensil spoon 2

Utensil knife 1

Utensil spork 4

即得到：

Category Item InventoryCount

------- ----- -------------

Beverage beer 9

Beverage milk 3

Utensil fork 7

Utensil spork 4

以下这段代码是完美解决方案，如果想增加筛选条件，只需要再 Group By 条件下增加相关内容的选择。这里使用的是 INNER JOIN clause 来规避可能出现的外部干扰。

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