diff options
| author | emkael <emkael@tlen.pl> | 2014-10-28 23:07:04 +0100 |
|---|---|---|
| committer | emkael <emkael@tlen.pl> | 2014-10-28 23:07:04 +0100 |
| commit | e76a6c56a285316a0aa376b4bed7c473ff4b154e (patch) | |
| tree | 3cb8bd25504a6efd3775574a6eefd7af9a779f02 /doc/sql.txt | |
| parent | e8ef92b0254909df8291d144d72bcb837817cf51 (diff) | |
* some overview queries
Diffstat (limited to 'doc/sql.txt')
| -rw-r--r-- | doc/sql.txt | 12 |
1 files changed, 12 insertions, 0 deletions
diff --git a/doc/sql.txt b/doc/sql.txt index cc9f187..16373b3 100644 --- a/doc/sql.txt +++ b/doc/sql.txt @@ -3,3 +3,15 @@ Useful queries: SELECT * FROM `rankings` JOIN drivers ON rankings._driver = drivers.id WHERE ranking >= (SELECT MAX(r.ranking) FROM rankings r WHERE r.rank_date <= rankings.rank_date) ORDER BY rank_date ASC - selects top rating progression + +SELECT drivers.driver, rankings.ranking, rankings.rank_date FROM rankings INNER JOIN (SELECT MAX(ranking) ranking, _driver FROM rankings GROUP BY _driver) r ON r.ranking=rankings.ranking AND r._driver=rankings._driver JOIN drivers ON rankings._driver = drivers.id ORDER BY rankings.ranking DESC + + - overall top peak ratings + +SELECT drivers.driver, rankings.ranking, rankings.rank_date FROM rankings INNER JOIN (SELECT MAX(rank_date) rank_date, _driver FROM rankings GROUP BY _driver) r ON r.rank_date=rankings.rank_date AND r._driver=rankings._driver JOIN drivers ON rankings._driver = drivers.id ORDER BY rankings.ranking DESC + + - highest exit ratings + +SELECT MAX(ranking), AVG(ranking), YEAR(rank_date) FROM rankings GROUP BY YEAR(rank_date) ORDER BY YEAR(rank_date) ASC + + - year-by-year rating inflation |