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.

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

Solarwinds

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)

Solarwinds
Previous Post
SQL SERVER – Answer – How to Convert Hex to Decimal or INT
Next Post
SQL SERVER – Difference Between ORIGINAL_LOGIN() and SUSER_SNAME()

Related Posts

No results found

37 Comments. Leave new

  • thanks Dave

    Reply
  • WITH Dup_LatLong AS
    (
    SELECT row_number() OVER (PARTITION BY
    LATITUDE, LONGITUDE ORDER BY UniqueValue) AS RowNumber, UniqueValue, convert(Varchar(50),LATITUDE)+convert(Varchar(50),LONGITUDE) Check_Cond
    FROM YourTable)
    Select UniqueValue,LATITUDE, LONGITUDE, convert(Varchar(50),LATITUDE)+convert(Varchar(50),LONGITUDE)
    from YourTable
    where convert(Varchar(50),LATITUDE)+convert(Varchar(50),LONGITUDE) in (Select Check_Cond from Dup_LatLong where RowNumber > 1)
    order by 7

    Reply
  • in above example i want result like

    Product Price Color
    1 Toy 100 Black
    2 Pen 100 Black
    3 Pencil 100 Blue, Red,Yellow
    4 Cup 300 Orange, Brown

    Reply
  • Excellent…

    Reply
  • Sir I have a problem,I have two tables,but I am not able to retrive the unique records from them.Its a hospital mgt application.I am using FULL OUTER JOIN grouping by datename(month,v.date)…..but output is wrong….kindly suggest me…plz.Thanks in Advance.

    Reply
  • V.Naveen Kumar
    April 22, 2014 11:28 am

    Thank u so much…..ii got answer what am looking.

    Reply
  • I have a query in vectorwise as below how can i convert to sqlserver
    “call vectorwise (COMBINE ‘tablea – tableb + tableb’)”

    Reply
  • Prashanth K. R
    March 8, 2015 6:18 pm

    Thank you for this great blog!

    Reply
  • Thank you very much. Very helpful post.

    Reply

Leave a Reply

Menu