Search code examples
mysqlsqlsubqueryalias

Do we have to use the alias of the table variable inside the subquery

I debugged the following code for long time and realized my mistake is that I initially omitted t. before variables inside the subquery. Is it a rule that we have to include alias inside subqueries?

Note: As the answer below stated, the real problem is not the lack of alias in the subquery but the use of reserved work "rank." If "rnk" is used instead, alias in the subquery is not needed.

WITH t AS(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Company ORDER BY Salary ASC) AS "rank" FROM 
Employee)
SELECT t.Id, t.Company, t.Salary
FROM t
JOIN (SELECT t.Company, MAX(t.rank) AS maxRank FROM t GROUP BY t.Company) s
ON t.Company=s.Company
WHERE CASE WHEN s.maxRank%2=0 THEN t.rank=s.maxRank/2 OR t.rank=s.maxRank/2+1
ELSE t.rank=CEIL(s.maxRank/2) END
Solution

  • No you don'z need as long you don't need to differentiate betweeen subquery and outer query .

    But please don't use reserved words for columns, else you need to encapsulate then

    WITH t AS(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Company ORDER BY Salary ASC) AS "rank" FROM 
Employee)
SELECT t.Id, t.Company, t.Salary
FROM t
JOIN (SELECT Company, MAX(`rank`) AS maxRank FROM t GROUP BY Company) s
ON t.Company=s.Company
WHERE CASE WHEN s.maxRank%2=0 THEN t.rank=s.maxRank/2 OR t.rank=s.maxRank/2+1
ELSE t.rank=CEIL(s.maxRank/2) END