CODEWITHSUNDEEP
sqlsql-servert-sql
user1941008
user1941008

Reputation: 383

SQL group by select

I am using SQL Server and I have a table with the following columns:

SessionId | Date | first name | last name

I would like to do group by sessionId and then get the row with the max date.

For example:

xxx | 21/12/2012 | f1 | l1
xxx | 20/12/2012 | f2 | l2
yyy | 21/12/2012 | f3 | l3
yyy | 20/12/2012 | f4 | l4

I would like to get the following rows:

xxx | 21/12/2012 | f1 | l1
yyy | 21/12/2012 | f3 | l3

Thank you

Upvotes: 10

Views: 11314

Answers (5)

Pragnesh
Pragnesh

Reputation: 1

    SELECT b.sessionid, 
       b.date, 
       b.fristname,         b.lastname 
FROM   (SELECT t2.sessionid, 
               Max(t2.date) AS Date 
        FROM   temp1 t2 
        GROUP  BY t2.sessionid) a, 
       temp1 b 
WHERE  a.sessionid = b.sessionid 
       AND a.date = b.date

Best way to find result is

Upvotes: 0

whytheq
whytheq

Reputation: 35607

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.SessionID
WHERE 
    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

Upvotes: 4

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

Try this:

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

Or:

SELECT 
  s.SessionId,
  s.Date,
  s.firstname,
  s.lastname 
FROM Sessions s
INNER 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.SessionsCount
FROM Sessions s
INNER 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;

Upvotes: 5

Bort
Bort

Reputation: 7638

Couple of options, one would be to use a CTE that ranks rows by Date:

Edited to include Session Count

WITH Sessions AS (
    SELECT SessionId, [Date], FirstName, LastName,
    ROW_NUMBER() OVER (PARTITION BY SessionId ORDER BY [Date] DESC) AS Ord
    FROM YourTable
)
SELECT S.SessionId, S.Date, S.FirstName, S.LastName, X.SessionCount
FROM Sessions S
INNER JOIN (
    SELECT SessionId, COUNT(*) AS SessionCount
    FROM Sessions
    GROUP BY SessionId 
) X ON X.SessionId = S.SessionId  
WHERE S.Ord = 1

Ranking Functions are your friend in situations like this, where you want to grab entire rows that meet certain 'ordered' criteria, like max date.

Upvotes: 0

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10073

Here we go, Simply this will do

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

SQL_FIDDLE_DEMO

Upvotes: 0

Related Questions