b*****e 发帖数: 4 | 1 table(player_name, score, time_stamp)
写一个query列出每个player的highest score跟这个record的time_stamp。 | d**e 发帖数: 6098 | 2 这是oracle的,至于用row_number, rank 还是dense_rank就看你自己的要求了。
select *
from (select player_name, score, time_stamp
row_number() over (parition by player_name
order by score desc) r
from the_table)
where r = 1;
【在 b*****e 的大作中提到】 : table(player_name, score, time_stamp) : 写一个query列出每个player的highest score跟这个record的time_stamp。
| B*****g 发帖数: 34098 | 3 also work for sql server 2005 or above
【在 d**e 的大作中提到】 : 这是oracle的,至于用row_number, rank 还是dense_rank就看你自己的要求了。 : select * : from (select player_name, score, time_stamp : row_number() over (parition by player_name : order by score desc) r : from the_table) : where r = 1;
| e********3 发帖数: 258 | 4 通用型Query:
select * from table join (select player_name, max(score) as maxscore from
table group by player_name) as t on (table.player_name = t.player_name and
table.score = t.maxscore) | n********5 发帖数: 323 | 5 一个group by 就搞定了?
通用query
select player_name, max(score) as maxscore, timestamp from table group by
player_name | d**e 发帖数: 6098 | 6 这个不能取 timestamp, 它不在group by 里面
【在 n********5 的大作中提到】 : 一个group by 就搞定了? : 通用query : select player_name, max(score) as maxscore, timestamp from table group by : player_name
| b*****n 发帖数: 221 | 7 通用query, 不知道对不对?
select table.player_name, table.score, timestamp
from table, (select player_name, MAX(score) AS maxscore) AS tmp
where table.player_name = tmp.playername AND table.score = tmp.maxscore; | L******h 发帖数: 1191 | 8 做数据库的话,大家都是找什么公司的工作?
【在 b*****e 的大作中提到】 : table(player_name, score, time_stamp) : 写一个query列出每个player的highest score跟这个record的time_stamp。
| c*******s 发帖数: 4 | 9 1 rowid+group by 2 row_number 3 rank |
|