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
Leave Comment