在 oracle 中查找第二个（或第 n 个）最新值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16053215/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
find second (or nth) latest value in oracle
提问by ajmalmhd04
I've referred How to get second largest or third largest entry from a table, but it only returns by looking through maxvalue.
我已经提到了如何从表中获取第二大或第三大条目，但它只能通过查看最大值来返回。
but here I need an sql query which uses max value of another field (here id which is a sequence).
但在这里我需要一个 sql 查询，它使用另一个字段的最大值（这里 id 是一个序列）。
Here is the table my_test
这是表 my_test
I need to get second largest unique value of sent_by, ie., 100according through id
我需要通过id获得sent_by 的第二大唯一值，即100
my query doesn't seems good, I've tried
我的查询似乎不太好，我试过了
SELECT sent_by
FROM MY_TEST
WHERE id =
(SELECT MAX(id)
FROM MY_TEST
WHERE id NOT IN
(SELECT id
FROM MY_TEST
WHERE sent_by =(SELECT sent_by FROM my_test WHERE id =(SELECT MAX(id) FROM MY_TEST))
)
); -- results 100
is there any other easy way to get the required? and how about if i need to get the 3rd?
有没有其他简单的方法来获得所需的？如果我需要获得第三个怎么样？
editing
编辑
Here i'll update my question to make you more clear.
在这里，我将更新我的问题，让您更清楚。
let us assume the given table is details of message transactions. let id be unique and is (sequence), sent_by is inserted with the individual's / person's id (like user_id). the sent_by column may / may not inserted with multiple rows with same user_id.
让我们假设给定的表是消息事务的详细信息。让 id 是唯一的并且是（序列），sent_by 插入个人/人的 id（如 user_id）。sent_by 列可能/可能不会插入具有相同 user_id 的多行。
if i need to take latest sent_by value: i can query
如果我需要获取最新的 sent_by 值：我可以查询
select sent_by from my_test where id= (select max(id) from my_test);-- to get 60
my question is to take the second latest sent_by value: (in the given table ,
我的问题是取第二个最新的 sent_by 值：（在给定的表中，
the 2nd latest sent_by value should be 100, as id 8, 9, 10 are same sent_by values
the 3rd latest sent_by value should be 4,
the 4th latest sent_by value should be 3
)
)
hope the question is clear now.
希望问题现在清楚了。
Thanks in advance:)
提前致谢：）
回答by TechDo
To find the second value, try:
要找到第二个值，请尝试：
select * from(
SELECT sent_by, row_number() over (order by sent_by desc) RNum
FROM MY_TEST
)x
WHERE RNum =2
for third
第三名
select * from(
SELECT sent_by, row_number() over (order by sent_by desc) RNum
FROM MY_TEST
)x
WHERE RNum =3
Yes... Now this makes a different question. Please check my try:
是的......现在这是一个不同的问题。请检查我的尝试：
SELECT DISTINCT SENT_BY FROM(
select ID, SENT_BY, dense_rank() OVER(ORDER BY MaxID DESC) RNum FROM(
SELECT ID, SENT_BY, max(ID) over (partition by sent_by order by ID desc) MaxID
FROM MY_TEST
)x
)xx where RNum=3
回答by A.B.Cade
If I understand you right, then try something like this:
如果我理解正确，请尝试以下操作：
select *
from(
select sent_by, row_number() over (order by sent_by desc, id asc) row_num
from MY_TEST) t
where row_num = 2 -- or 3 ... n
UPDATE
更新
Try this:
尝试这个：
select *
from(
select sent_by,
rank() over (order by max(id) desc) rk
from MY_TEST
group by sent_by) t
where rk = 2 -- or 3 .. n
回答by user2225399
method 1:
方法一：
select sent_by, rownum, from my_test where(rowid,0) in (select rowid, mod(rownum,4) from my_test);
this would return every 4th row. If you want random rows replace subquery with rownums in an in clause.
这将每 4 行返回一次。如果您希望随机行在 in 子句中用 rownums 替换子查询。
method 2:
方法二：
select sent_by, rownum from (select sent_by , rownum from MY_TEST) v1 where mod(v1.rownum,4)=0;
Method 3:
方法三：
select sent_by, rownum, from my_test group by sent_by, rownum having mod(rownum,n)=0 or rownum=2-n
with any of these methods to select say 5th and 9th rows this could change 1st method to the following
使用这些方法中的任何一种来选择第 5 行和第 9 行，这可以将第一种方法更改为以下方法
select sent_by, rownum, from my_test where rownum in(5,9);
