blog

Home / DeveloperSection / Blogs / Distinct Data in SQL Server

Distinct Data in SQL Server

AVADHESH PATEL2613 01-Nov-2012

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

Distinct Data in SQL Server

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

Distinct Data in SQL Server


Updated 18-Sep-2014
Avadhesh Kumar Patel District Project Manager - Aligarh 14 months work experience in Panchayati Raj Department Sector as District Project Manager & 12 months work experience in IT Sector as Software Engineer. :-)

Leave Comment

Comments

Liked By