CODEWITHSUNDEEP
sqldatabaseoracle-database
ritesh singh chauhan
ritesh singh chauhan

Reputation: 21

How to fetch nth recent value?

I have a table named "friendbook_post" with the following fields: text, id, type, author, date_time. There are many entries in the table .

Now I want to fetch 2nd post(according to time) of every author (if exist) along with author id(author) and date.

I tried many queries but nothing helped me .

Upvotes: 0

Views: 51

Answers (1)

David Faber
David Faber

Reputation: 12485

You need to use an analytic function (in this case, ROW_NUMBER()) along with a subquery:

SELECT * FROM (
    SELECT text, id, type, author, date_time, ROW_NUMBER() OVER ( PARTITION BY author ORDER BY date_time DESC ) AS the_post_num
      FROM friendbook_post
) WHERE the_post_num = 2

This will give the 2nd newest post (2nd most recent). If you want to get the 2nd oldest post then ORDER BY date_time ASC. And 2 can be replaced with any n. Note that if an author doesn't have a 2nd post then the above query won't return anything for that author.

Hope this helps.

Upvotes: 2

Related Questions