Is the following acceptable in SQL:
SELECT P.pid P.cnt
FROM Professors P
WHERE P.cnt <= Max(SELECT P2.cnt FROM Professors P2)
I am asking because this is provided as an answer to a problem in a midterm by the professor. I have actually tried it in SQL and wasn't allowed, but I just wanted to make sure.
Syntactically, I would have written it like this:
SELECT P.pid, P.cnt
FROM Professors P
WHERE P.cnt <= (SELECT MAX(P2.cnt) FROM Professors P2)
Logically, it doesn't really seem to accomplish much since the condition will always be true.
This won't work. I don't think it is standard ANSI SQL
You can try to rewrite it like this
SELECT P.pid P.cnt
FROM Professors P
WHERE P.cnt <= (SELECT max(P2.cnt) FROM Professors P2)
Yes, queries are allowed within aggregate functions. What was the error you got when you tried it? The thing that sticks out to me is a missing comma after P.pid in SELECT P.pid P.cnt.
I may be missing something obvious myself, but to me, this seems like a bit of extra SQL that is doing nothing. I read it as "Select information about Professsors for all Professors that are less than or equal to the Professor with the greatest count" - I.E., all professors. Why not just eliminate the WHERE entirely?
