Return rows of the latest 'streak' of data

Go To StackoverFlow.com

4

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.

2012-04-04 02:51
by hpoydar
Are the numbers in id sequential without holes? Or are the dates in played? What is your version of PostgreSQL - Erwin Brandstetter 2012-04-04 03:24


3

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

2012-04-04 03:39
by Erwin Brandstetter
Nice. I ended up dropping the assumption that the ids are in sequence by using the 'played' date field instead - hpoydar 2012-04-04 20:46
@hpoydar: Yup, works with played just as well - Erwin Brandstetter 2012-04-04 22:21
Ads