Reputation: 383
I am using MS-SQL 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. in additional to that i would like to count the sessionsId.
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 | 2
yyy | 21/12/2012 | f3 | l3 | 2
Thanks.
Upvotes: 1
Views: 103
Reputation: 29071
Try this:
SELECT sessionid, a.date, firstname, lastname, COUNT(sessionid) sessioncnt
FROM (SELECT * FROM sessions ORDER BY sessionid, date DESC) AS a
GROUP BY sessionid;
Upvotes: 0
Reputation: 10083
This will simply 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
Sample Answer:
DATE1 FIRST_NAME LAST_NAME
21/12/2012 f1 l1
21/12/2012 f3 l3
Upvotes: 0
Reputation: 79979
Try this:
SELECT
s.SessionId,
s.Date,
s.firstname,
s.lastname,
m.SessionsCount
FROM Sessions s
INNER JOIN
(
SELECT SessionID, COUNT(SessionID) AS SessionsCount, MAX(Date) LatestDate
FROM sessions
GROUP BY SessionID
) m ON m.SessionID = s.SessionID
AND m.LatestDate = s.Date;
This will give you:
| SESSIONID | DATE | FIRSTNAME | LASTNAME | SESSIONSCOUNT |
-----------------------------------------------------------------
| xxx | 21/12/2012 | f1 | l1 | 2 |
| yyy | 21/12/2012 | f3 | l3 | 2 |
Upvotes: 2