sql - How can I generate a new row out of two other rows in Postgres? -

i have data in postgres table looks this:

1 apple datetime1 2 orange datetime2 3 apple datetime3 4 orange datetime4 5 apple datetime5 6 orange datetime6 . 

the datetime in ascending order , majority of times apple rows inserted first , orange second exceptions have catch , eliminate.

what practically need postgres query pair apples , oranges only:

1 apple datetime1 2 orange datetime2 3 apple datetime3 4 orange datetime4 5 apple datetime5 6 orange datetime6 

apples should never paired other apples , oranges should never paired other oranges.

there couple of conditions:

1) in newly generated rows apple should first , orange second.

2) pair apple , orange rows closest datetimes , ignore other rows.

for example if have original data looking this:

1 apple datetime1 2 apple datetime2 3 orange datetime3 4 orange datetime4 

pair

2 apple datetime2 3 orange datetime3 

and ignore rows

1 apple datetime1 4 orange datetime4 

any ideas how in postgres?

solution cte & window function:

with x (     select *           ,lead(tbl) on (order id) y     tbl     ) select x.id,     x.fruit,   x.dt      , (y).id, (y).fruit, (y).dt   x  fruit = 'apple' ,    (y).fruit = 'orange' order  x.id; 

could done subquery well, per request @wildplasser. :)

i pick whole "next" row per window function lead(). note syntax parenthesis access columns of composite (row) type y.