Advertisements
Home > SQL > SQL Query to fetch top 1 record from each group

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

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

🙂

Advertisements
Categories: SQL Tags: , , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: