Clever Geek Handbook

How to get individual entries in a group by

My SQL Server table looks like this

 Id     Date                Msg
 1    01-01-2015             A
 1    02-01-2015             B
 2    03-01-2015             C

I want to write a query to get the result by following

   Id          Date            Msg
    1           02-01-2015      B
    2           03-01-2015      C

I'm trying to

 SELECT Id,Max(Date) , Msg
 Group By Id, Msg

This also returns the first line to me. I want Msg date for Max for a specific ID.

Any Suggestion Please

+3


source to share


2 answers


Use Window Function 

SELECT Id,
       Date,
       Msg
FROM   (SELECT *,
               Row_number()OVER (partition BY Id ORDER BY [Date] DESC ) rn
        FROM   tablename) a
WHERE  rn = 1


Another way is to find the max date

for Id

in subquery

and then the join

result with the main table. Try it.

SELECT Id,
       Date,
       Msg
FROM   tablename a
       JOIN (SELECT Id,
                    Max([Date])[Date]
             FROM   tablename
             GROUP  BY Id) B
         ON a.Id = b.id
            AND a.[Date] = b.[Date]
+2


source


Easy way to get max date string in where clause

Here's a simple example of a request;



SELECT * FROM   TABLE  T1  
WHERE  T1.TIME = (SELECT MAX(TIME) FROM TABLE  WHERE ID = T1.ID )
0


source






More articles:


All Articles