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

Question

I am googled and stackoverflowed high and low and unable to find any specific solutions to display 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 of the result 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 efficient SQL to achieve this? Any advice is appreciated!

Answer 1

row_number bi_id, created_at. , . postgres, window.

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;