求一条SQL语句!!急啊!救救!
表结构: id datetime1 number(数量)
数据
id datetime1 number
1 2008-1-1 5
2 2008-1-2 5
3 2008-1-3 8
4 2008-1-4 7
5 2008-1-5 7
select top 2 * from 表名 order by [datetime1] desc
用了上面的语句会找出最后2条数据,但是我想要的是找出最后2条数量不一样的数据
也就是要找出
3 2008-1-3 8
5 2008-1-5 7
这样语句要怎么改? 高手帮帮忙啊!忠心感谢!
[解决办法]
-->生成测试数据 declare @tb table([id] int,[datetime1] Datetime,[number] int)Insert @tbselect 1,'2008-1-1',5 union allselect 2,'2008-1-2',5 union allselect 3,'2008-1-3',8 union allselect 4,'2008-1-4',7 union allselect 5,'2008-1-5',7Select top 2 max([id]),max([datetime1]),[number] from @tbgroup by [number] order by 2 desc/*(5 row(s) affected) number----------- ----------------------- -----------5 2008-01-05 00:00:00.000 73 2008-01-03 00:00:00.000 8(2 row(s) affected)*/
[解决办法]
Select top 2 max([id]) as [id] ,convert(nvarchar(10),max([datetime1]),120) as [datetime1],[number] from @tbgroup by [number] order by 2 desc /*id datetime1 number----------- ---------- -----------5 2008-01-05 73 2008-01-03 8(2 row(s) affected)*/
[解决办法]
select top 2 * from 表名 where id in (select max(id) from 表名 group by number) order by [datetime1] desc