How to find max value and its associated field values in SQL?

Question

Say I have a list of student names and their marks. I want to find out the highest mark and the student, how can I write one select statement to do that?

Answer 1

Assuming you mean marks rather than remarks, use:

select name, mark
from students
where mark = (
    select max(mark)
    from students
)

This will generally result in a fairly efficient query. The subquery should be executed once only (unless your DBMS is brain-dead) and the result fed into the second query. You may want to ensure that you have an index on the mark column.