Using the row_Number(), Get the distinct records with no Duplicates – SQL Server

September 25, 2012

This was an interesting SQL concept i came across to check for the Distinct of records and get a Distinct A,B,C and also to get the Distinct (B), from the result of distinct combination. I am sure i have confused you!!

OK.. let me put this in a more simple form, We derive at combination of distinct Product, Price, Size. This shall give a result something as below,

And to get the same scenario in my sample table i query into the Product table, taking only a single product which gives all the distinct combinations as below (taken only one product for instance here),

But what if i need the one combination to get the one with the maximum price, which can be attained using the Row-Number() function as in my sample query,

SELECT ProductID,ReferenceOrderLineID,ReferenceOrderID, ActualCost
FROM
(SELECT Production.TransactionHistory.*,
ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ActualCost DESC) AS RN
FROM Production.TransactionHistory) AS t
WHERE RN = 1 

And here we go with the distinct and only transaction of every product with the maximum cost as is shown below,

So we can use the same where the Distinct and group by clause might be help less. Thanks for a read, share and enjoy 🙂

Advertisements

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: