SQL SERVER – Query to Get Unique Distinct Data Based on Condition – Eliminate Duplicate Data from Resultset

Seems like in T-SQL world the issue with Duplicate Records never an old topic. Today lets  quickly go over another question which made it to my mailbox 3 times this week.

Question: How do I display only unique records from my table?

The natural reaction will be to suggest DISTINCT or GROUP BY. However, not all the questions can be solved by DISTINCT or GROUP BY. Let us see the following example, where a user wanted only latest records to be displayed. Let us see the example to understand further.

What the user wanted was not to display every duplicate records but wanted to display the record which had highest Price value. I like this kind of simple puzzle. This kind of puzzles has multiple solutions and I like to work on them. However, in this particular case, I was able to quickly come to resolve as I used ROW_NUMBER function in the solution.

Create a sample table.

USE TempDB
GO
CREATE TABLE Table1 (ID INT, Product VARCHAR(100), Price INT, Color VARCHAR(100))
GO
INSERT INTO Table1
SELECT 1, 'Toy', 100, 'Black'
UNION ALL
SELECT 2, 'Pen', 100, 'Black'
UNION ALL
SELECT 3, 'Pencil', 100, 'Blue'
UNION ALL
SELECT 4, 'Pencil', 100, 'Red'
UNION ALL
SELECT 5, 'Pencil', 200, 'Yellow'
UNION ALL
SELECT 6, 'Cup', 300, 'Orange'
UNION ALL
SELECT 7, 'Cup', 400, 'Brown'
GO
SELECT Product, Price, Color
FROM Table1
GO

Select the data without duplicate records as well select the data with maximum price.

SELECT Product, Price, Color
FROM Table1
GO
SELECT Product, Price, Color
FROM
(SELECT Table1.*,
ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Price DESC) AS RN
FROM Table1) AS t
WHERE RN = 1
ORDER BY ID
GO

Clean up act.

DROP TABLE Table1
GO

Please feel free to share your solution as a comment. I will share the solutions on blog with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)