Return rows of the latest 'streak' of data

Question

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.

Are the numbers in id sequential without holes? Or are the dates in played? What is your version of PostgreSQL? — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 4, 2012 at 3:24

Erwin Brandstetter · Accepted Answer · 2012-04-04 03:46:40Z

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

Nice. I ended up dropping the assumption that the ids are in sequence by using the 'played' date field instead.

Collectives™ on Stack Overflow

Return rows of the latest 'streak' of data

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related