查询排序的问题,请高手给解决一下!
表A 中有N个字段,主要的是id、name、paix这两个字段是int型的字段name是字符型,id是主键name和paix里的记录都不唯一，我现在想排序，要实现的功能是：取出前十条记录，所有查询出的记录如果id和paix的数字大就排在最前面，而且这十条记录的name字段不能重复。
有哪位高手能给出个实现的sql语句！
[解决办法]
id优先：
select top 10 * from a a1
where not exists (
select 1 from a
where name=a1.name
and (id> a1.id
or (id=a1.id
and paix> a1.paix)
)
)
order by id desc,paix desc
--其实id是主键，这样写就可以
select top 10 * from a a1
where not exists (
select 1 from a
where name=a1.name
and id> a1.id
)
order by id desc
---------------------------------
paix优先：
select top 10 * from a a1
where not exists (
select 1 from a
where name=a1.name
and (paix> a1.paix
or (id> a1.id
and paix=a1.paix)
)
)
order by paix desc,id desc
[解决办法]
select top 10 *
from (
select max(id) as ID,name,max(paix) as paix
from a
group by name) a
order by ID desc,paix desc
[解决办法]
select top 10 name,max(id) from a
group by name order by max(id) desc
