Rajeev Pentyala – Technical Blog on Power Platform, Azure and AI

I have a SQL table ‘Market’ with 3 Groups (i.e., Fruits, Vegetable and Meat) and below is the data

My requirement is

• From each group fetch the top 1 item by ‘Priority’ column (i.e., Priority = 1) like below

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

🙂