咨询SQL语句,order by,top相关

[ahpei]

表T的数据如下：
sName dDate
a 2008-10-07
a 2008-10-06
b 2008-10-06
b 2008-10-06
c 2008-10-07
d 2008-10-06
c 2008-10-01
...
以此类推，假如该表记录为n。

现在，需要取出dDate最近的10个不重复的sName值。

[iamduo]

沙发一下，让我想想。

[iamduo]

select top 10 sName,
(select Max(dDate) from tab as tt2 where tt1.sName=tt2.sName)
as dDate from tab as tt1

试试看。

[fangsp]

楼上的正解
顶一个

[xiao_jun_0820]

select top 10 * from (select sName,max(ddate) as tdate from tmptable group by sName) b order by b.tdate desc

测试数据:
a 2008-10-7
a 2008-10-6
b 2008-10-6
b 2008-10-6
c 2008-10-7
d 2008-10-8
d 2008-10-9
e 2008-10-6
f 2008-10-7
g 2008-10-7
g 2008-10-6
i 2008-10-6
h 2008-10-7
h 2008-10-7
k 2008-10-9
k 2008-10-6
y 2008-10-7
o 2008-10-9
n 2008-10-6

[zouyx317]

在Sql 2000 中调试了一下
Declare @T table(SName varchar(10),dDate datetime)
insert into @t
select 'a','2008-10-07'
union all Select 'a', '2008-10-06'
union all Select 'b', '2008-10-06'
union all Select 'b', '2008-10-09'
union all Select 'c', '2008-10-07'
union all Select 'd', '2008-10-06'
union all Select 'c', '2008-10-01'
union all Select 'r', '2008-10-06'
union all Select 'z', '2008-10-07'
union all Select 'k', '2008-10-06'
union all Select 'j', '2008-10-01'
union all Select 'i', '2008-10-06'
union all Select 'h', '2008-10-07'
union all Select 'h', '2008-10-08'
union all Select 'g', '2008-10-06'
union all Select 'f', '2008-10-01'
union all Select 'g', '2008-10-09'
union all Select 'f', '2008-10-04'
select * from @T

select top 10 Sname,Max(dDate)as dDate from @T group by Sname


SName dDate
---------- ------------------------------------------------------
a 2008-10-07 00:00:00.000
a 2008-10-06 00:00:00.000
b 2008-10-06 00:00:00.000
b 2008-10-09 00:00:00.000
c 2008-10-07 00:00:00.000
d 2008-10-06 00:00:00.000
c 2008-10-01 00:00:00.000
r 2008-10-06 00:00:00.000
z 2008-10-07 00:00:00.000
k 2008-10-06 00:00:00.000
j 2008-10-01 00:00:00.000
i 2008-10-06 00:00:00.000
h 2008-10-07 00:00:00.000
h 2008-10-08 00:00:00.000
g 2008-10-06 00:00:00.000
f 2008-10-01 00:00:00.000
g 2008-10-09 00:00:00.000
f 2008-10-04 00:00:00.000

（所影响的行数为 18 行）

Sname dDate
---------- ------------------------------------------------------
a 2008-10-07 00:00:00.000
b 2008-10-09 00:00:00.000
c 2008-10-07 00:00:00.000
d 2008-10-06 00:00:00.000
f 2008-10-04 00:00:00.000
g 2008-10-09 00:00:00.000
h 2008-10-08 00:00:00.000
i 2008-10-06 00:00:00.000
j 2008-10-01 00:00:00.000
k 2008-10-06 00:00:00.000

（所影响的行数为 10 行）

[mathsfan]

11楼的写法还真牛B...

[xingyongjian]

问题没有表述清除!

[DumDum]

select sname,max(dDate) from table group by sName

[luckyhh]

select distinct top 10 sName from T order by dDate desc

[DumDum]

select top 10 sname,max(dDate) from table group by sName order by sName

[yf520gn]

什么数据库？ORA还是sql server?
楼上的TOP只能用在SQL SERVER数据库中~~
ORA写法如下：
select distinc sName from
(select sName,dense_rank() over( order by dDate desc) rn
from T)
where rn <=10

[yf520gn]

select sName from
(select sName,dDate,ROW_NUMBER() over( order by dDate desc) rn
from (select sName,max(dDate) dDate from T group by sName))
where rn <=10
刚才的有点错

[zhaofuxiao321]

取经来了

[MarsLand]

select a.sname from (select distinct sname from T) a,T b where a.sname=b.sname and b.dDate = (select max(dDate) from T where sName = a.sname) order by b.dDate DESC
已测试过，给分吧

[ljhcy99]

select top 10 max(dDate),sName
from T
group by sName
order by dDate desc

[Alex-16888]

学习中