如何从表中获取第二大或第三大条目 [英] How to get second largest or third largest entry from a table

问题描述

谁能告诉我如何从Oracle表中找出第N个最大条目?

Can anyone please tell me how to find out the N th largest entry from a table in Oracle?

就像我们可以使用的最大 MAX(column_name)有没有一种有效的方法来找到第n个最大的?

Like for largest we can use MAX(column_name) is there any efficient way to find nth largest also?

推荐答案

SELECT *
FROM (
  SELECT some_column, 
         row_number() over (order by your_sort_column desc) as row_num
  FROM some_table
) t
WHERE row_num = 3

如果您希望在your_sort_column中有多个行具有相同的值，则还可以使用rank()函数

If you expect more than one row to have the same value in your_sort_column you can also use the rank() function

SELECT *
FROM (
  SELECT some_column, 
         rank() over (order by your_sort_column desc) as row_rank
  FROM some_table
) t
WHERE row_rank = 3

此行可能返回多行.

这篇关于如何从表中获取第二大或第三大条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！