如何在不排序的情况下获得一列中值最高的n行

Question

我有一个SQL表:

C1 | C2 | C3
aa | bb | 21
gg | hh | 53
dd | yy | 37

我想要C3中值最高的前5000行，但我想要我的数据按C1排序我该怎么做呢?谢谢。

Answer 1

使用解析式ROW_NUMBER函数:

SELECT C1, C2, C3
FROM (
  SELECT C1, C2, C3, ROW_NUMBER() OVER (ORDER BY C3 DESC) AS C3Rank
  FROM myTable
)
WHERE C3Rank <= 500
ORDER BY C1