CODEWITHSUNDEEP
sqlsql-servergroup-by
user1941008
user1941008

Reputation: 383

SQL Query with counting

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

Answers (3)

Saharsh Shah
Saharsh Shah

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

Mariappan Subramanian
Mariappan Subramanian

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

Live Demo

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

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;

SQL Fiddle Demo

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

Related Questions