SQL to get the 2nd (nth) record for each group (postgresql or mysql)

Question

I googled and stackoverflowed high and low and not able to find any concrete solutions to show the 2nd (nth) record for each group.

Consider the following table (order by created_at desc):

----------------------------------------------
bid_id | status    | created_at
----------------------------------------------
1      | cancelled | 2015-10-03 
1      | awarded   | 2015-10-02
1      | pending   | 2015-10-01
2      | pending   | 2015-10-01
3      | denied    | 2015-10-02
3      | pending   | 2015-10-01

The result output should look like this (grouped by bid_id):

bid_id | status    | created_at
----------------------------------------------
1      | awarded   | 2014-10-02
3      | pending   | 2014-10-01

What is an efficient SQL to achieve this? Any tips are greatly appreciated!

Answer 1

You can use row_number to assign row numbers in each bi_id partition ordered by created_at column. Then you can simply select the 2nd or whichever row you need. This works in postgres as it supports window functions.

select bid_id, status, created_at
from
(
select bid_id, status, created_at,
row_number() over(partition by bid_id order by created_at desc) as rn
from tablename
) x
where x.rn = 2;