Sunday 20 November 2011

How to retrieve TOP Distinct values in SQL Server

I have recently got a requirement from one of our customers to show recently used distinct items for a particular report. While writing the query, I found out it is not straight forward as there is a little cache “recent items” in the requirement.

This can be achieved by using a simple trick as shown in the below example. To illustrated the scenario, I have create a sample table with data

Sample Data

CREATE TABLE SaleItem ( ItemName NVARCHAR(30),SaleDate DATETIME)

insert into SaleItem SELECT 'Book',GETDATE()
insert into SaleItem SELECT 'Book',GETDATE()
insert into SaleItem SELECT 'Book',GETDATE()
insert into SaleItem SELECT 'Pencil',GETDATE()
insert into SaleItem SELECT 'Pencil',GETDATE()
insert into SaleItem SELECT 'Pencil',GETDATE()
insert into SaleItem SELECT 'Pen',GETDATE()
insert into SaleItem SELECT 'Pen',GETDATE()
insert into SaleItem SELECT 'Pen',GETDATE()

SQL Query

WITH ItemList AS
(
SELECT
IL.ItemName,
IL.SaleDate,
ROW_NUMBER() OVER (ORDER BY IL.SaleDate DESC) as 'RankID'
FROM
SaleItem IL
)
SELECT DISTINCT TOP 30
IL.ItemName,
IL.SaleDate
FROM ItemList IL
WHERE NOT EXISTS (Select 1 FROM ItemList IL2
WHERE ( IL2.ItemName = IL.ItemName )
AND IL.RankID < IL2.RankID)
ORDER BY SaleDate DESC

The trick is assigning RankID to each row and eliminate the duplicate row  by comparing the RankID while selecting the records.

4 comments:

Anonymous said...

your query gives worong results;
you can use this simple query

SELECT ItemName, MAX(SaleDate)
FROM SaleItem c
GROUP BY ItemName
ORDER BY MAX(SaleDate) DESC

Unknown said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

Unknown said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

Unknown said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03