CODEWITHSUNDEEP
sqlsql-server-2008
Aneesh Daniel
Aneesh Daniel

Reputation: 533

Top 1 distinct rows from a table

ID Username     ModifiedDate

1  A.BEENA     2009-12-07 04:48:17.980
2  A.BEENA     2009-11-17 06:02:27.443
3  Abhilash    2009-12-07 04:48:17.980
4  abhilash.r  2009-12-07 04:48:17.980
5  AJI         2009-11-17 06:02:27.443
6  haris       2009-12-07 04:48:17.980
7  haris       2009-11-17 06:02:27.443

I want to select details of all distinct users order by ModifiedDate.

I need output like this

   1 A.BEENA      2009-12-07 04:48:17.980
   3 Abhilash     2009-12-07 04:48:17.980
   4 abhilash.r   2009-12-07 04:48:17.980
   5 AJI          2009-11-17 06:02:27.443
   6 haris        2009-12-07 04:48:17.980

Please help me

Upvotes: 4

Views: 2177

Answers (3)

Dane
Dane

Reputation: 9827

Here you go:

CREATE TABLE #temp(ID int, Username varchar(50), ModifiedDate datetime)

INSERT INTO #temp(ID, Username, ModifiedDate)
VALUES
(1,  'A.BEENA',     '2009-12-07 04:48:17.980'),
(2,  'A.BEENA',     '2009-11-17 06:02:27.443'),
(3,  'Abhilash',    '2009-12-07 04:48:17.980'),
(4,  'abhilash.r',  '2009-12-07 04:48:17.980'),
(5,  'AJI',         '2009-11-17 06:02:27.443'),
(6,  'haris',       '2009-12-07 04:48:17.980'),
(7,  'haris',       '2009-11-17 06:02:27.443')

SELECT t.Username, t.ModifiedDate
FROM #temp t
WHERE NOT EXISTS (SELECT 1 FROM #temp WHERE Username = t.Username AND ModifiedDate > t.ModifiedDate)

Upvotes: 0

augustine
augustine

Reputation: 1022

Use the following query:

WITH CTE (DuplicateCount,Username,ModifiedDate) AS
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY Username ORDER BY ModifiedDate DESC) AS DuplicateCount,
        Username,
        ModifiedDate
    FROM YourTable
)
SELECT *
FROM CTE
WHERE DuplicateCount = 1

Upvotes: 7

MartW
MartW

Reputation: 12538

SELECT Username, MAX(ModifiedDate) AS LastModified
FROM MyTable
GROUP BY Username

This will give the output you mention, which appears to show the most recent date for each username.

Upvotes: 3

Related Questions