Table's alias created in FROM clause isn't recognized in WHERE clause-CodePudding

The task is "Find the university Id where the number of employees graduated from that university is maximum over all university"

The schemas is like this

Graduate ( EmpId: NUMERIC REFERENCES Employee(EmpId), 
           UnivId: NUMERIC REFERENCES University(UnivId), 
           GradYear: NUMERIC)

University ( UnivId: NUMERIC, UnivName: VARCHAR(40))

Employee (EmpId: NUMERIC, 
          EmpName: VARCHAR(40))

My query:

     SELECT Temp.UnivId
     FROM  (SELECT G.UnivId, COUNT(*) as Num
            FROM Graduate G, Employee E
            WHERE G.EmpId = E.EmpId
            GROUP BY G.UnivId) AS Temp
     WHERE Temp.Num = (SELECT MAX(Temp.Num) FROM Temp);

When I run this query in psql console and the software is PostgresSQL, it return an error say relation "temp" does not exist and it point the Temp at the very end. Does anyone knows why ?

CodePudding user response：

You should be using RANK here:

WITH cte AS (
    SELECT g.UnivId, RANK() OVER (ORDER BY COUNT(e.EmpId) DESC) rnk
    FROM Graduate g
    INNER JOIN Employee e
        ON g.EmpId = e.EmpId
    GROUP BY g.UnivId
)

SELECT UnivId
FROM cte
WHERE rnk = 1;

Note that this approach also handles ties nicely, should they occur.

The problem with your current approach is that you are referring to the subquery in the WHERE clause as if it's a standalone table, which it is not. You could move the Temp subquery to a CTE, and then your approach can be made to work:

WITH Temp AS (
    SELECT G.UnivId, COUNT(*) as Num
    FROM Graduate G, Employee E
    WHERE G.EmpId = E.EmpId
    GROUP BY G.UnivId
)

SELECT Temp.UnivId
FROM Temp
WHERE Temp.Num = (SELECT MAX(Temp.Num) FROM Temp);

CodePudding user response：

You can access columns of the query you have aliased Temp, but you cannot select from it, because you have not created a view. If you want to create an ad-hoc view, use a WITH clause for this.

You should not use comma separated joins by the way. This was the syntax used in the 1980s and some years on until explicit joins ([INNER] JOIN, LEFT [OUTER] JOIN, etc.) made it into the SQL standard in 1992. Why join the employee table anyway?

Here is one way to solve this:

select univid, count(*)
from graduate
group by univid
order by count(*) desc
fetch first row with ties;

Here is another:

select univid, cnt
from
(
  select univid, count(*) as cnt, max(count(*)) over () as max_cnt
  from graduate
  group by univid
) t
where cnt = max_cnt;

And here is what you tried:

with t as
(
  select univid, count(*) as cnt
  from graduate
  group by univid
)
select *
from t
where cnt = (select max(cnt) from t);

CodePudding user response：

It should be simpler. Move your subquery into a CTE then order by num descending and pick the topmost result. You do not need to join Graduate and Employee too.
Btw temp is a reserved word so better do not use it as an identifier/name.

with tmp as
(
 select univid, count(*) as num
 from graduate
 group by univid
)
select univid
from tmp
order by num desc limit 1;

I think that CTEs make SQL code more readable yet you can write the same without a CTE.

select univid
from 
(
 select univid, count(*) as num
 from graduate
 group by univid
) tmp
order by num desc limit 1;

However if ties are an issue you better use the rank approach of @TimBiegeleisen, still w/o the join.