find second (or nth) latest value in oracle

Question

I've referred How to get second largest or third largest entry from a table, but it only returns by looking through max value.

but here I need an sql query which uses max value of another field (here id which is a sequence).

Here is the table my_test enter image description here

I need to get second largest unique value of sent_by, ie., 100 according through id

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.

if i need to take latest sent_by value: i can query

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 ,

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:)

Try using distinct of the row and sort by desc and select the second row. — SenthilPrabhu, Apr 17 '13 at 06:53
It's not clear why [this answer](http://stackoverflow.com/a/4483657/1083652) you've refered to isn't good. All you need to do is specify in the `your_sort_column` part the 2 columns - `SENT_BY, ID` — A.B.Cade, Apr 17 '13 at 07:05

A.B.Cade · Accepted Answer · 2013-04-18T06:49:25.460

4

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

Here is a sqlfiddle demo

edited Apr 18 '13 at 06:49

answered Apr 17 '13 at 07:16

A.B.Cade

16,735
1
37
53

@ A.B.Cade, I've already checked with this, Please check the updated question, to make you the question more clear – ajmalmhd04 Apr 18 '13 at 03:27
Well, that's too a wonderful answer. and as mixed query of techdo's answer. Thanks for a good query. – ajmalmhd04 Apr 18 '13 at 06:28
That's a great answer... enriches my knowledge! – TechDo Apr 18 '13 at 08:40
@ A.B.Cade, how would I get the id of that corresponding rank? say 7 for rk = 2, 5 for rk = 3 etc. I've used sub-query by using max(), is there any other good way? Thanks. – ajmalmhd04 May 04 '13 at 07:21

TechDo · Answer 2 · 2013-04-18T05:24:51.507

3

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

edited Apr 18 '13 at 05:24

answered Apr 17 '13 at 06:43

TechDo

18,398
3
51
64

my question is not at all doing with order by sent_by column, i'll update my to question to make you clear, as in my requirement, i need the 3rd latest value as 4. – ajmalmhd04 Apr 18 '13 at 03:20

score 0 · Answer 3 · answered Apr 17 '13 at 06:46

0

Analytic functions. ROW_NUMBER() OVER() NTH_VALUE

answered Apr 17 '13 at 06:46

realnumber3012

1,062
6
10

score 0 · Answer 4 · answered Apr 18 '13 at 17:04

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.

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

select sent_by, rownum, from my_test where rownum in(5,9);

,at first , the question is not for selecting the nth row, instead it should take nth latest row, so it should works perfectly only with id, but not with rowid, or rownum. — ajmalmhd04, Apr 19 '13 at 02:18

find second (or nth) latest value in oracle

editing

4 Answers4