SQL group by select

Try this:

WITH MAXSessionsAS(  SELECT     *,    ROW_NUMBER() OVER(PARTITION BY SessionID ORDER BY Date DESC) rownum  FROM Sessions)SELECT  SessionId,  Date,  firstname,  lastname FROM MAXSessionsWHERE rownum = 1;

Or:

SELECT   s.SessionId,  s.Date,  s.firstname,  s.lastname FROM Sessions sINNER JOIN(   SELECT SessionID, MAX(Date) LatestDate   FROM sessions   GROUP BY SessionID) MAxs  ON maxs.SessionID  = s.SessionID       AND maxs.LatestDate = s.Date;

Update: To get the count of the sessions, you can do this:

SELECT   s.SessionId,  s.Date,  s.firstname,  s.lastname,  maxs.SessionsCountFROM Sessions sINNER JOIN(   SELECT SessionID, COUNT(SessionID), SessionsCount, MAX(Date) LatestDate   FROM sessions   GROUP BY SessionID) MAxs  ON maxs.SessionID  = s.SessionID       AND maxs.LatestDate = s.Date;

Here is a live example of Mahmoud's answer - SQL Fiddle

Here is the same, just using a sub-query:

SELECT a.*FROM     #Table a    INNER JOIN        (        SELECT             SessionID,            [mx] = MAX([Date])              FROM #Table        GROUP BY SessionID                          ) b        ON            a.[SessionId ] = b.SessionID AND            a.[Date] = b.mx;

HERE IS THE SQL FIDDLE FOR THE ABOVE SUB-QUERY VERSION

You can also use EXISTS - this is my favourite:

SELECT      a.*,     c.CNT FROM      #Table a     INNER JOIN         ( --to return a count of sessionIds        SELECT            SessionID,           [CNT] = COUNT(*)             FROM #Table        GROUP BY SessionID                          ) c          ON a.SessionID = c.SessionIDWHERE     EXISTS        (        SELECT 1        FROM #Table b        WHERE            a.[SessionId] = b.SessionID AND            a.[Date] > b.[Date]         )

HERE IS THE SQL FIDDLE WITH THE ADDITIONAL COUNT INCLUDED

Here we go, Simply this will do

select a.date1,a.first_name,a.last_namefrom(select row_number() over(partition by SessionId order by SessionId) rnk,date1,first_name,last_namefrom table1) a where a.rnk=1

SQL_FIDDLE_DEMO