Question: I have some data in a Postgres table that looks like this:

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

```

The datetime is always in ascending order and the majority of times the apple rows are inserted first and orange second with some exceptions that I have to catch and eliminate.

What I practically need is a Postgres query that will pair apples and oranges only: 

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

```

Apples should never be paired with other apples and oranges should never be paired with other oranges.

There are couple of conditions:

1) In the newly generated rows apple should always be first and orange second.

2) Always pair apple and orange rows with the closest datetimes and ignore the other rows.

For example if I have the original data looking like 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 to do this in Postgres?

Answer: 
Solution with CTE & window function:

```
WITH x AS (
    SELECT *
          ,lead(tbl) OVER (ORDER BY id) AS y
    FROM tbl
    )
SELECT x.id,     x.fruit,   x.dt
     , (y).id, (y).fruit, (y).dt
FROM   x
WHERE  fruit = 'apple'
AND    (y).fruit = 'orange'
ORDER  BY x.id;

```

Could be done as subquery just as well, but per [request from @wildplasser](https://stackoverflow.com/questions/10883651/how-can-i-generate-a-new-row-out-of-two-other-rows-in-postgres#comment14185141_10883651). :)

I pick the whole "next" row per window function [`lead()`](http://www.postgresql.org/docs/current/interactive/functions-window.html). Note the syntax with parenthesis to access columns of the composite (row) type `y`.
```
select 
    t0.id, t0.fruit, t0.datetime, 
    t1.id, t1.fruit, t1.datetime
from t t0
inner join t t1 on 
    t0.fruit = 'apple' 
    and 
    t1.fruit = 'orange'
    and
    t0.datetime < t1.datetime
order by t1.datetime - t0.datetime
limit 1

```