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.
Assuming (as you don't tell) that
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á.
id
sequential without holes? Or are the dates inplayed
? What is your version of PostgreSQL - Erwin Brandstetter 2012-04-04 03:24