Home > DeveloperSection > Blogs > Distinct Data in SQL Server

Distinct Data in SQL Server


Database Database 
Ratings:
0 Comment(s)
 1238  View(s)
Rate this:

Distinct Data in SQL Server

How do I display only unique records from my table?

For demonstration first we create a table

--USE DATABASE

USE AVI

--CREATE TABLE

CREATE TABLE Product_Info (ID INT, Product VARCHAR(100), Price INT, Color VARCHAR(100))

GO

INSERT INTO Product_Info

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 Product_Info

GO

Screen Shot


Now we going to filter the distinct record based on column name

SELECT Product, Price, Color

FROM

(SELECT Product_Info.*,

ROW_NUMBER() OVER (PARTITION BY Price ORDER BY Price DESC) AS RN

FROM Product_Info) AS t

WHERE RN = 1

ORDER BY ID

GO

Screen Shot


Don't want to miss updates? Please click the below button!

Follow MindStick