Question

SQL查询问题,取最大值

现有一个表,结构为id,wordID,price,time,我想取出同一wordID的price为最大值数据,如果price一样的话,就按时间升序来取,如有以下记录:
id,wordID,price,time
(1,1,2,2006-11-2)
(2,1,3,2006-11-3)
(3,1,3,2006-11-4)
(4,2,2,2006-11-2)
(5,2,3,2006-11-3)
(6,2,3,2006-11-4)
则取出:
(3,1,3,2006-11-4)
(6,2,3,2006-11-4)
我不懂SQL语句怎么写,大家帮帮忙,多谢了!!!
lgamoy - 经理 五级 :
select max(price) max_price,wordID,max([time]) max_time from table group by wordID这一语句得出的是某一wordID的最大price和最大time,但price最大的记录time并不一定是最大的,我要的结果是首先要price最大的,如果price相同,才去比较time.
不管怎样,都多谢各位了!我也想了好久都想不出.

Answer 1

你这个用一条语句还不太好写

试试这个，不过 First 函数不是 SQL 函数，只有 Access 支持
SELECT First(id) AS ID, [wordID], MAX([price]) AS price, time
FROM [tablename]
GROUP BY [wordID],[time]
ORDER BY [time]

建议你用存储过程来写

Answer 2

如果你是同一wordID日期没有重复的话，可以这样。
select * from table a,(select max(price) max_price,wordID,max([time]) max_time from table group by wordID) b where
a.wordID=b.wordID and a.price=b.max_price and a.[time]=b.max_time

Answer 3

select max(price) max_price,time from table group by wordID
order by max_price,time

Answer 4

Select * From [tablename] where id =(select max(id) from [tablename]) order by [time]

Answer 5

select max(id),worlid from table1 order by time grop by wordid