| |
| Products |
| Category |
Item |
Price |
| Book |
ASP |
10 |
| Book |
C# |
20 |
| Book |
VB |
30 |
| Food |
Pizza |
90 |
| Food |
Bread |
70 |
| Food |
Rice |
50 |
| Car |
Benz |
400 |
| Car |
BMW |
800 |
| Car |
Ford |
600 |
|
---- how to Select the TOP n Rows For Each Group
SELECT *
FROM ( SELECT
ROW_NUMBER() OVER (
PARTITION BY Category ORDER BY Price DESC ) AS 'RowNumber', *
FROM Products
) dt
WHERE RowNumber <= 2
| Output |
| RowNumber |
Category |
Item |
Price |
| 1 |
Book |
VB |
30 |
| 2 |
Book |
C# |
20 |
| 1 |
Car |
BMW |
800 |
| 2 |
Car |
Ford |
600 |
| 1 |
Food |
Pizza |
90 |
| 2 |
Food |
Bread |
70 |
---- how to Select the TOP 1 Row For Each Group
;WITH TopN AS
(
SELECT NTILE(3) OVER (PARTITION BY Category ORDER BY Price desc) GroupRank, *
FROM Products
)
SELECT * FROM TopN
WHERE GroupRank = 1
| Output |
| GroupRank |
Category |
Item |
Price |
| 1 |
Book |
VB |
30 |
| 1 |
Car |
BMW |
800 |
| 1 |
Food |
Pizza |
90 |
---- to find the Top 2 Expensive Item's Average Price for each group
SELECT Category, AVG(Price) AS Top2ItemsAvgPrice
FROM ( SELECT
ROW_NUMBER() OVER (
PARTITION BY Category ORDER BY Price DESC ) AS 'RowNumber', *
FROM Products
) dt
WHERE RowNumber <= 2
GROUP BY Category
| Output |
| Category |
Top2ItemsAvgPrice |
| Book |
25 |
| Car |
700 |
| Food |
80 |
|