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

  Modified On Sep-18-2014 01:24:06 PM

Leave Comment