How can I get the name of the student who has the max grant?

Question

I get this error:

Msg 147, Level 15, State 1, Line 3
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Answer 1

Use TOP:

select top (1) name
from student s
order by grant desc;

The above returns one row always, even when there are ties for the highest grant. If you want all such rows, you can add the with ties modifier:

select top (1) with ties name
from student s
order by grant desc;

Answer 2

You can use:

SELECT [name]
FROM [student]
WHERE [grant] = (SELECT max([grant]) FROM [student])

Answer 3

The following code can be correct if only one student has the max grant:

select top (1) name
    from student s
    order by grant desc;

if you have two or more student with the same grant, you should write as below:

select * from
(
select 
id,
name,
Dens_Rank() over (order by grant desc) student_rank
)temp where temp.student_rank=1

Answer 4

Please find below query -

SELECT * FROM student WHERE ROWNUM < 2 ORDER BY grant DESC;