Return rows of the latest 'streak' of data

gaps-and-islands, postgresql, sql

Solution

Assuming (as you don't tell) that

- there are exactly two distinct values for `result`: `(W, L)`.

- `id` is sequential in the sense that the latest entry has the highest `id`.

This would do the job:

SELECT *
FROM   tbl
WHERE  id > (
    SELECT max(id)
    FROM   tbl
    GROUP  BY result
    ORDER  BY max(id)
    LIMIT  1
    );

This gets the latest id for `W` and `L`, the earlier of the two first. So a `LIMIT 1` gets the last entry of the opposite outcome. Rows with an `id` higher than that form the latest streak. Voilá.

Problem

Given a simple table with the following data: ``` id | result | played ----+--------+------------ 7 | L | 2012-01-07 6 | L | 2012-01-06 5 | L | 2012-01-05 4 | W | 2012-01-04 3 | W | 2012-01-03 2 | L | 2012-01-02 1 | W | 2012-01-01 ``` How would I write a query to return the lastest losing or winning streak of rows using PostgreSQL? In this case, I'm looking for a result of: ``` id | result | played ----+--------+------------ 7 | L | 2012-01-07 6 | L | 2012-01-06 5 | L | 2012-01-05 ``` I'm guessing the answer is to use `lag()` and partition syntax, but I can't seem to nail it down.

Original source