CODEWITHSUNDEEP
sqlmysql
Roy
Roy

Reputation: 603

How to select rows based on values in other rows

I have a database full of sports results. I'd like to select some results based on some characteristics of the previous results. Here's the database structure:

CREATE TABLE `results` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `date` DATETIME NOT NULL ,
    `home_score` INT NOT NULL ,
    `away_score` INT NOT NULL ,
    `home_team_id` INT NOT NULL ,
    `away_team_id` INT NOT NULL
);

So I'd like to do queries like "find results where a team has won their two previous home games" - i.e. for a specific home_team_id order by by date, then select each row where in the previous two rows home_score > away_score.

I know this is a bit complicated so any pointers as to how to solve this will be massively appreciated. I currently have a version in PHP (selects all rows and then performs this type of query) but performance is very slow and it uses a huge amount of memory (there are over 20,000 rows in the database).

EDIT: Thanks for a couple of neat answers. Ideally, though, I'd like to be able to run queries on all columns, not just looking at W, D or L. A more complicated example would be "find all results where the home team had won each of their five previous home games by at least two goals and the away team had lost each of their away games by at least one goal."

Upvotes: 5

Views: 3329

Answers (2)

reko_t
reko_t

Reputation: 56430

If you want this to perform well you should link the consecutive results in the table, eg. have a prev_result_id column, that you set to the previous result whenever you insert a new result. That way you can solve the problem as following:

SELECT * FROM
    (SELECT * FROM results WHERE home_team_id = ID ORDER BY date DESC LIMIT 1) r1
    INNER JOIN results r2 ON r2.id = r1.prev_result_id
    WHERE r1.home_score > r1.away_score AND r2.home_score > r2.away_score

EDIT. To improve this further, you can also have next_result_id field (that is indexed), and have it NULL for the last result (since there's no next result yet). That way you can get rid of the sub-query in FROM, since you don't have to order by date at all:

SELECT * FROM results r1 INNER JOIN results r2 ON r2.id = r1.prev_result_id
WHERE r1.home_team_id = ID AND r1.next_result_id IS NULL
AND r1.home_score > r1.away_score AND r2.home_score > r2.away_score

This is especially a good way if you want to find ALL teams that has won their previous to matches, because now you can simply omit the r1.home_team_id = ID from the where clause, and it should give you one row per every team that has won their previous two matches.

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 452967

Depending on the full range of queries that you need I might consider denormalising. e.g. in the team table add columns such as

home_performance = 'WWDLWW'
away_performance = 'WWLLWL'
overall_performance = 'WWWWDLLLWWWL'

Then adhoc queries such as won their last 2 home games could be answered by doing

home_performance LIKE '%WW'

Or won 4 in a row would be overall_performance LIKE '%WWWW%'

These derived columns could be populated by using GROUP_CONCAT

Upvotes: 2

Related Questions