SQL Query to fetch top 1 record from each group
I have a SQL table ‘Market’ with 3 Groups (i.e., Fruits, Vegetable and Meat) and below is the data
SQL Table – Market
My requirement is
- From each group fetch the top 1 item by ‘Priority’ column (i.e., Priority = 1) like below
| Name | Group |
Priority |
| Apple | Fruits | 1 |
| Fish | Meat | 1 |
| Potato | Vegetable | 1 |
The approach is to provide ‘Rank’ using ROW_NUMBER to records in each group and get the Top 1 record
Below is the Query
— Create a temporary table with ‘Rank’ column
;WITH TempTbl AS
(
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY [Group]
ORDER BY [Priority] ASC
) AS Rank
FROM [dbo].[Market]
)
— Select the Rank = 1 records
SELECT *
FROM TempTbl
WHERE
Rank = 1
🙂
Categories: SQL
group, Row_number, SQL, top 1 row
Comments (0)
Trackbacks (0)
Leave a comment
Trackback
Rajeev Pentyala - Microsoft Power Platform 