求SQL语句

[feifei0504]

有一个表
ID 序号 时间
1 1 2017-11-17
2 1 2017-11-17
3 2 2017-11-17
4 2 2017-11-17
5 3 2017-11-17
6 3 2017-11-17
7 4 2017-11-17
8 5 2017-11-17
我想取出序号重复的项目中id最大的一条
结果如下
ID 序号 时间
2 1 2017-11-17
4 2 2017-11-17
6 3 2017-11-17
语句应该怎么写？

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[序号] int,[时间] Date)
Insert #T
select 1,1,'2017-11-17' union all
select 2,1,'2017-11-17' union all
select 3,2,'2017-11-17' union all
select 4,2,'2017-11-17' union all
select 5,3,'2017-11-17' union all
select 6,3,'2017-11-17' union all
select 7,4,'2017-11-17' union all
select 8,5,'2017-11-17'
Go
--测试数据结束
;WITH ctea AS (
Select *,ROW_NUMBER()OVER(PARTITION BY 序号 ORDER BY ID ) AS num from #T
),cteb AS (
SELECT 序号,MAX(num) AS num FROM ctea WHERE num<>1 GROUP BY 序号
)
SELECT  ID ,
        ctea.序号 ,
        时间
FROM    cteb
        JOIN ctea ON ctea.num = cteb.num
                     AND ctea.序号 = cteb.序号 

[日月路明]

select * from ta join（select ID=max(ID) from ta group by 序号) as t on ta.ID=t.ID

[xzxmustwin]

引用 1 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[序号] int,[时间] Date)
Insert #T
select 1,1,'2017-11-17' union all
select 2,1,'2017-11-17' union all
select 3,2,'2017-11-17' union all
select 4,2,'2017-11-17' union all
select 5,3,'2017-11-17' union all
select 6,3,'2017-11-17' union all
select 7,4,'2017-11-17' union all
select 8,5,'2017-11-17'
Go
--测试数据结束
;WITH ctea AS (
Select *,ROW_NUMBER()OVER(PARTITION BY 序号 ORDER BY ID ) AS num from #T
),cteb AS (
SELECT 序号,MAX(num) AS num FROM ctea WHERE num<>1 GROUP BY 序号
)
SELECT  ID ,
        ctea.序号 ,
        时间
FROM    cteb
        JOIN ctea ON ctea.num = cteb.num
                     AND ctea.序号 = cteb.序号 

新手学习了。发现版主偏爱使用公用表表达式

[学海无涯-回头是岸]

with t1 as
(select max(id) as id_1,xh from table group by xh)
select * from table where exists (select 1 from t1 where table.id =t1.id_1 and table.xh=t1.xh)

[DDDDDSH]

我也是来学习的...