我想从info表中选择按不同sort0和sort1分类的每一类别记录的第一条记录(按时间adddate排序),但下面的是错误的,因为order by adddate desc不能用于单个select,但将order by adddate desc用于后面有不能达到我的要求,请问正确的应该怎么写??谢谢
(select top 1 infoid,title=left(title,16),adddate from info
where sort0= 'xxxxx ' and sort1= 's1 ' order by adddate desc)
union
(select top 1 infoid,title=left(title,16),adddate from info
where sort0= 'xxxxx ' and sort1= 's2 ' order by adddate desc)
union
(select top 1 infoid,title=left(title,16),adddate from info
where sort0= 'aaaaa ' and sort1= 's3 ' order by adddate desc)
union
(select top 1 infoid,title=left(title,16),adddate from info
where sort0= 'aaaaa ' and sort1= 's4 ' order by adddate desc)
------解决方案--------------------
--update
Select * from
(select top 1 infoid,title=left(title,16),adddate from info
where sort0= 'xxxxx ' and sort1= 's1 ' order by adddate desc) t
union
Select * from (select top 1 infoid,title=left(title,16),adddate from info
where sort0= 'xxxxx ' and sort1= 's2 ' order by adddate desc) t
union
Select * from (select top 1 infoid,title=left(title,16),adddate from info
where sort0= 'aaaaa ' and sort1= 's3 ' order by adddate desc) t
union
Select * from (select top 1 infoid,title=left(title,16),adddate from info
where sort0= 'aaaaa ' and sort1= 's4 ' order by adddate desc) t