Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 357
user1941008 Avatar asked Jan 01 '13 14:01

user1941008


People also ask

Can we use GROUP BY in SELECT?

You can use a SELECT command with a GROUP BY clause to group all rows that have identical values in a specified column or combination of columns, into a single row. You can also find the aggregate value for each group of column values.

Can we use SELECT statement in GROUP BY in SQL?

The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

How do you SELECT and group data in SQL?

The SQL GROUP BY clause allows us to group individual data based on defined criteria. You can group individual data by one or more table columns. In order to do the grouping properly, you often need to apply aggregate functions to the column(s) within the SQL SELECT statement.

Can we use * in GROUP BY?

You can, but the "GROUP BY" clause is used for grouping together sets of rows, so it does not make sense for your question (or anything that involves a "SELECT *").


1 Answers

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;
like image 140
Mahmoud Gamal Avatar answered Nov 15 '22 21:11

Mahmoud Gamal