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: 
> 
> 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.
> 
> 
> 

If I understand correctly, you want to find if there are any two consecutive rows with the same fruit, right?

If so, you can do it like this:

```
WITH Q AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY datetime) R
    FROM YOUR_TABLE
)
SELECT *
FROM Q Q1 JOIN Q Q2 ON Q1.R = Q2.R - 1
WHERE Q1.fruit = Q2.fruit;

```

In plain English: join every row with the next one (where the meaning of "next" is determined by the `datetime` ordering) and check if their fruits match. You can play with it in this [SQL Fiddle](http://sqlfiddle.com/#!1/1a8a8/20).

*BTW, this is not a good database design. You should consider a redesign so "invalid" situation you described above is naturally prevented by the database structure itself.*
```
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

```