急高分求一查询SQL解决思路

急...急....急....高分求一查询SQL
我想通过分组SessionID查询最大的TalkDuration那条数据

如
TalkDuration SequenceID SessionID
22 1 1
0 2 1
0 1 2
30 2 2
0 1 3
0 2 3
0 1 4
3 1 5

要的数据应该是
TalkDuration SequenceID SessionID
22 1 1
30 2 2
0 1 3
0 1 4
3 1 5
要求是
1.相同的sessionid,TalkDuration不相同显示最大的TalkDuration那条数据
2.相同的sessionid,TalkDuration相同只显示一条
3.没有相同sessionid的全部都显示

[解决办法]

SQL code

create table tb(TalkDuration int,SequenceID int,SessionID int)insert into tbselect 22 ,1 ,1 union allselect 0 ,2 ,1 union allselect 0 ,1 ,2 union allselect 30 ,2 ,2 union allselect 0 ,1 ,3 union allselect 0 ,2 ,3 union allselect 0 ,1 ,4 union allselect 3 ,1 ,5goselect t.* from tb t where not exists     (select 1 from tb     where SessionID = t.SessionID and ((TalkDuration = t.TalkDuration and SequenceID > t.SequenceID)        or TalkDuration > t.TalkDuration))/*要的数据应该是TalkDuration SequenceID SessionID22 1 130 2 20 1 30 1 43 1 5*/drop table tb/*************TalkDuration SequenceID  SessionID------------ ----------- -----------22           1           130           2           20            2           30            1           43            1           5(5 行受影响)**********************/select *from(    select *,px=row_number() over (partition by SessionID order by TalkDuration desc,SequenceId)    from tb)twhere px = 1/**********************TalkDuration SequenceID  SessionID   px------------ ----------- ----------- --------------------22           1           1           130           2           2           10            1           3           10            1           4           13            1           5           1(5 行受影响)
[解决办法]
SQL code
create table je(TalkDuration int, SequenceID int, SessionID int)insert into jeselect 22, 1, 1 union allselect 0, 2, 1 union allselect 0, 1, 2 union allselect 30, 2, 2 union allselect 0, 1, 3 union allselect 0, 2, 3 union allselect 0, 1, 4 union allselect 3, 1, 5;with t as(select row_number() over(partition by SessionID,TalkDuration order by getdate()) rn,TalkDuration,SequenceID,SessionID from je)select a.TalkDuration,a.SequenceID,a.SessionIDfrom t ainner join(select max(TalkDuration) maxTalkDuration,SessionID from t where rn=1 group by SessionID) bon a.TalkDuration=b.maxTalkDuration and a.SessionID=b.SessionIDwhere a.rn=1 order by a.SessionIDTalkDuration SequenceID  SessionID------------ ----------- -----------22           1           130           2           20            1           30            1           43            1           5(5 row(s) affected)