My teacher presented us with the following challenge.
There is a table that has 3 columns:
- Team
- Date of the match
- Won or Lost
Write an SQL Query (no PL/SQL), that returns the team or teams that has/have the highest winning streak and the number of consecutive wins.
Only 'regular' SQL is allowed, so no Oracle-specific functions.
I have already (I think) solved this, but I was wondering what solutions you guys would come up with.
I'll post my solution later.
Below I've provided some sample DDL.
CREATE TABLE Games(Team VARCHAR2(10), MatchDate DATE, WL VARCHAR2(1));
INSERT INTO Games VALUES ('TeamA','1-JAN-2012','W');
INSERT INTO Games VALUES ('TeamA','2-JAN-2012','W');
INSERT INTO Games VALUES ('TeamA','3-JAN-2012','W');
INSERT INTO Games VALUES ('TeamB','4-JAN-2012','W');
INSERT INTO Games VALUES ('TeamB','5-JAN-2012','W');
INSERT INTO Games VALUES ('TeamB','6-JAN-2012','W');
INSERT INTO Games VALUES ('TeamA','7-JAN-2012','L');
INSERT INTO Games VALUES ('TeamA','8-JAN-2012','W');
INSERT INTO Games VALUES ('TeamA','10-JAN-2012','W');
Edited by: popovitsj on Apr 3, 2013 7:50 AM
Edited by: popovitsj on Apr 3, 2013 8:21 AM
Edited by: popovitsj on 3-apr-2013 12:23
Edited by: popovitsj on 3-apr-2013 12:36