急求一条查询语句

[pbyhz]

有一个表，其中id是自动编号，s_id 为数字，现在想根据s_id的由大到小的顺序取出前3个不重复的name,并按s_id的由大到小的顺序排列，该如何写语句？
id name s_id

1 AAA 21
2 BBB 21
3 CCC 21
4 AAA 23
5 BBB 23
6 CCC 23
7 DDD 25
8 EEE 32
9 CCC 55
10 FFF 11
11 GGG 34
12 HHH 34
13 BBB 34

[paoluo]

Select * from TableName A
Where Not Exists(Select 1 from TableName Where s_id=A.s_id And id<A.id Having Count(*)>2) Order By s_id,id

[淡蓝冰]

SELECT to 3 NAME FROM (SELECT * from table1 ORDER BY s_id ) bb GROUP BY name

[GRLD8888]

select top 3 s_id,name from table1 group by name order by s_id desc

[pbyhz]

上面两为的都不行啊。paoluo(一天到晚游泳的鱼) 取出来的还有是重复的，woxiangfang(小黑黑脸)出现这样的错误提示“除非同时指定了 TOP，否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。”

[paoluo]

可能我沒明白你的意思。

上面列出的是你的結果，還是你的數據？？

你是要取三條數據？？

還是每組s_id 取三條數據？？

[paoluo]

如果是我說的第一種。

Try:

Select Top 3 Name from TableName A
Where Not Exists (Select 1 from TableName Where name=A.name)
Order By s_id Desc

[pbyhz]

上面是我模拟的数据，如果按照我的意思取出的数据的话就应该从里面取出第9条，第12条，第13条数据，其实就是类似豆瓣网上的最新标签的意思

[GRLD8888]

更正一下：
select top 3 name,max(s_id) mm from table1
group by name order by mm desc

[pbyhz]

xiaonvjing(飞扬) 正解，谢谢大家了

[paoluo]

暈死，漏了條件。怪不得有問題。


Select Top 3 Name from TEST A
Where Not Exists (Select * from TEST Where Name=A.name And id<>A.id)
Order By s_id Desc

[senhanxiao]

SELECT TOP 3 [NAME],[S_ID] FROM (SELECT [NAME],MAX(S_ID) AS S_ID FROM TABLE1 GROUP BY [NAME]) W ORDER BY S_ID DESC