sql group语句

[zealothasu]

表a
其中有
NNo varchar(20),
STime datetime,
Timestamp - timestamp
n1,
n2,
n3,
n4...


NNO, STime是主键
Timestamp是时间戳

现在想用一个比较有效率的方法找出每个NNo最新的时间STime的记录
(注意，Timestamp大的不一定时间STime是最新的)

我是这样写的

select NNO, STime, n1, n2....
from a, (select NNO, MAX(STime) stime from a) b
where a.NNo = b.NNo and a.STime = b.STime


这样是不是效率比较低？
因为数据量比较大
但是每次的结果只有300条记录
有没有更好的方法？

[WangZWang]

先在NNO, STime上建立索引

Select NNO, STime, n1, n2....
from a inner Join (select NNO, MAX(STime) stime
from a group by NNO) b
On a.NNo = b.NNo and a.STime = b.STime

[zjcxc]

select NNO, STime, n1, n2....
from a, (select NNO, MAX(STime) stime from a) b
where a.NNo = b.NNo and a.STime = b.STime

--就是这样写的啦

--在字段NNo,STime上建立复合索引
--在字段STime上建立倒排序的单独索引.

[netcoder]

晕，老邹也有失手的时候
----------------------------------
select NNO, MAX(STime) stime from a
----------------------------------
语句根本不对呀--

[netcoder]

select a.NNO, a.STime
from a ,(select NNO, MAX(STime) stime from a group by nno) b
where a.NNo = b.NNo and a.STime = b.STime

[zjcxc]

同意楼上.

不是第一次,经常忘记一些东西

唉,人老了就这样.

[loverpyh]

select a.NNO, a.STime
from a ,(select NNO, MAX(STime) stime from a group by nno) b
where a.NNo = b.NNo and a.STime = b.STime
字段STime上建立倒排序索引.