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开发者_Go百科:02:27.443
   6 haris        2009-12-07 04:48:17.980

Please help me

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

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.

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)