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 ?
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);
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.
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);