Calculating Winning and Losing Streaks?

[spperl]

Hi,

I have a table called 'results' which is made up of the following fields

id
team_id
result (Win, Loss, Draw)

I'm trying to construct a query that will show what streak a particular team is on. e.g.

Team 1 has won the last 5
Team 1 has drawn the last 0
Team 1 has lost the last 0

Team 2 has won the last 0
Team 2 has drawn the last 2
Team 2 has lost the last 0

Does anyone have any ideas on how to achieve this with a single query on MySQL? (it can be assumed that the results are ordered by id)

Any help or pointers would be greatly appreciated.

Many thanks.

 

[DonQuichote]

If you want that answer from that database server and not from code, I think your best option is to write stored procedures or functions.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)