我正在使用sql Server,并且我有一个表与以下列：
SessionId | Date | first name | last name
我想通过sessionId进行分组,然后获取具有最大日期的行.
例如：
xxx | 21/12/2012 | f1 | l1 xxx | 20/12/2012 | f2 | l2 yyy | 21/12/2012 | f3 | l3 yyy | 20/12/2012 | f4 | l4
我想得到以下行：
xxx | 21/12/2012 | f1 | l1 yyy | 21/12/2012 | f3 | l3
谢谢
解决方法
尝试这个：
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;
要么：
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;
更新：要获得会议次数,可以这样做：
SELECT
s.SessionId,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;