How to properly do aliasing in correlated subquery

user_651174 0
Viewed 31

For the following subquery:

;WITH results as (
    SELECT 'DAL' as team, 2010 as season, 7 as wins union
    SELECT 'DAL' as team, 2011 as season, 11 as wins union
    SELECT 'DAL' as team, 2012 as season, 11 as wins union
    SELECT 'NE' as team, 2012 as season, 15 as wins union
    SELECT 'DET' as team, 2010 as season, 6 as wins
)

SELECT team, wins, season,
    wins - (select max(wins) from results as r where r.season=results.season) difference_from_best_subquery
FROM results;

What is the proper way to alias the results so its clear that you want to pass "this" value into the results? The above works, but I'd hope that I could do something a bit more readable (to myself) such as:

SELET MAX(wins) FROM table WHERE wins=$this.wins

What's the best approach to this?

mysql sql
1 Answers

On MySQL 8+, I would just use MAX as an analytic function:

SELECT team, wins, season,
    MAX(wins) OVER (PARTITION BY season) - wins AS diff_from_best
FROM results;

Another way to avoid the correlated subquery would be to use a join approach to a subquery which finds the max wins for each season:

SELECT
    r1.team,
    r1.wins,
    r1.season,
    r2.max_wins - r1.wins AS diff_from_best
FROM results r1
INNER JOIN
(
    SELECT season, MAX(wins) AS max_wins
    FROM results
    GROUP BY season
) r2
    ON r2.season = r1.season;
user_18632290
Related