Select distinct top 4 records from table

sql, sql-server

Solution

WITH topList
AS
(
    SELECT  sno, city, state, country, code, date,
            ROW_NUMBER() OVER(PARTITION BY code ORDER BY DATE DESC) rn
    FROM    TableName
)
SELECT  TOP 4 sno, city, state, country, code, date
FROM    topList
WHERE   rn = 1
ORDER   BY DATE DESC

- SQLFiddle Demo

OUTPUT

╔═════╦══════════╦═══════╦═════════╦══════╦════════════╗
║ SNO ║   CITY   ║ STATE ║ COUNTRY ║ CODE ║    DATE    ║
╠═════╬══════════╬═══════╬═════════╬══════╬════════════╣
║   2 ║ Houston  ║ TX    ║ US      ║ 2234 ║ 1/6/2013   ║
║   5 ║ Brooklyn ║ NY    ║ US      ║ 1234 ║ 1/4/2013   ║
║   4 ║ Chicago  ║ IL    ║ US      ║ 1244 ║ 1/3/2013   ║
║   3 ║ LA       ║ CA    ║ US      ║ 1123 ║ 1/2/2013   ║
╚═════╩══════════╩═══════╩═════════╩══════╩════════════╝

Problem

I have the following SQL Server Table I want latest all Top 4 distinct codes from the below table Remember I want all columns to be returned not just codes column. ``` sno city state country code date 1 new york NY US 1234 1/1/2013 2 Houston TX US 2234 1/6/2013 3 LA CA US 1123 1/2/2013 4 Chicago IL US 1244 1/3/2013 5 Brooklyn NY US 1234 1/4/2013 6 Dallas TX US 2234 1/5/2013 ``` My following select query is returning duplicate codes, but I want distinct latest codes. ``` select top 4 * from table1 where code in (select distinct code from table1) ``` Any help is greatly appreciated.

Original source