Query to retrieve only record for each user for last date entered

Question

If I had a table like this:

Username    Comment    Date
-------------------------------    
   X        test1      1-1-2015      
   X        test2      1-3-2015      
   X        test3      1-6-2015      
   Y        test1      1-4-2015      
   Y        test2      1-5-2015      
   Y        test3      1-9-2015

and I need to get an output result like the below

Username    Comment    Date
----------------------------
    X       test3      1-6-2015      
    Y       test3      1-9-2015

Output:

only retrieve record for X & Y based on the last date (get user name & comment for the last date that entered)

Is there is any way by using SQL Server 2014 to get result like this?

Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Aug 23 '15 at 20:10

score 3 · Answer 1 · answered Aug 23 '15 at 20:11

3

You can use row_number to select the most recent comment per username

select * from (
  select * , row_number() over (partition by username order by date desc) rn
  from mytable
) t1 where rn = 1

answered Aug 23 '15 at 20:11

FuzzyTree

32,014
3
54
85

score 0 · Answer 2 · answered Aug 23 '15 at 20:12

You can use a CTE (Common Table Expression) like this:

;WITH CTE AS
(
  SELECT
      Username, Comment, [Date],
      RowNum = ROW_NUMBER() OVER (PARTITION BY Username ORDER BY [Date] DESC)
)
SELECT
    Username, Comment, [Date]
FROM 
    CTE
WHERE
    RowNum = 1

This selects only the most recent entry for each Username

Also: be aware that Date is a reserved keyword in T-SQL and it should not be used for your own column names! Try to use something more meaningful - like CommentDate or something!

Query to retrieve only record for each user for last date entered

2 Answers2