showing the details of the student with the maximum marks in oracle 11g R2

goal

I have a student table. I need to show the details viz. name of the student who has obtained the maximum marks in a test.

definition of the tables

the following is the code for the definition of the tables:(I have created this using the text to ddl option from http://www.sqlfiddle.com)

CREATE TABLE STUDENT
    ("NAME" varchar2(1), "MARKS" int)
;

some records that I inserted:

INSERT ALL 
    INTO STUDENT ("NAME", "MARKS")
         VALUES ('A', 17)
    INTO STUDENT ("NAME", "MARKS")
         VALUES ('B', 18)
    INTO STUDENT ("NAME", "MARKS")
         VALUES ('C', 19)
    INTO STUDENT ("NAME", "MARKS")
         VALUES ('D', 90)
    INTO STUDENT ("NAME", "MARKS")
         VALUES ('E', 50)
    INTO STUDENT ("NAME", "MARKS")
         VALUES ('F', 23)
SELECT * FROM dual
;

please help me with the query that will achieve this.

specs oracle 11g R2 on http://www.sqlfiddle.com

on a windows machine.

Solution

The quick (and inefficient) way is to select the max and use it in the WHERE clause.

select *
from student
where marks = (
    select max(marks)
    from student
);

In the case of a tie, you'll get multiple rows.

If you're worried about performance, only do one pass on the table by using analytic functions. For example: Analytic functions for “the attribute from the row with the max date”