How to get second row in PostgreSQL?

Question

I have next table:

COLUMN_NAME DATA_TYPE     PK    NULLABLE
inc_id      bigint        YES   NO
dt_cr       timestamp     NO    NO
email       varchar(255)  NO    YES

email column is not unique, so I should use GROUP BY email.

Question: How can I get from this table minimal date, next row after minimal date and email related to this dates?

I read about first_value and nth_value functions, code listed below, but seems like it doesn't work correct.

SELECT J1.email, J2.first_date, J2.second_date 
FROM (
    SELECT email 
    FROM orders 
    GROUP BY email) J1
LEFT OUTER JOIN (
    SELECT email, 
        first_value(dt_cr) over (order by dt_cr ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) first_date, 
        NTH_VALUE(dt_cr, 2) over (order by dt_cr ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) second_date 
    FROM orders) J2  
ON (J1.email=J2.email);

Any ideas?

--edited maybe it can be done with rank() function..

Answer 1

Try with row_number function.

Within whole table:

select * 
from(select *, row_number() over(order by dt_cr) rn from tableName)t 
where rn <= 2

Or within email:

select * 
from(select *, row_number() over(partition by email order by dt_cr) rn from tableName)t 
where rn <= 2

Answer 2

This query uses WITH construction that works similar to sub-queries. Investigate this query with EXPLAIN before use in production because it may be slow on big tables:

WITH orders AS (
    SELECT 
          email
        , first_value(dt_cr) OVER wnd1 AS min_date
        , nth_value(dt_cr, 2) OVER wnd1 AS second_date 
    FROM orders
    WINDOW wnd1 AS ( PARTITION BY email ORDER BY email, dt_cr)
)
SELECT DISTINCT * 
FROM orders
WHERE second_date IS NOT NULL;