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

Question

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.

What do you want in the case of a tie? – Alkini Mar 29 '13 at 15:29 — Alkini, Mar 29 '13 at 15:29

score 3 · Accepted Answer · edited May 23 '17 at 12:28

3

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”

edited May 23 '17 at 12:28

Community

1
1

answered Mar 29 '13 at 15:31

Alkini

1,449
2
12
26

Thanks!! this was the correct query!! – IcyFlame Mar 30 '13 at 08:28

score 1 · Answer 2 · answered Mar 29 '13 at 17:14

The easiest way to do this is by ordering the data and taking the first row:

select *
from (select s.*
      from student s
      order by mark desc
     ) s
where rownum = 1;

An alternative method is to use analytic functions:

select *
from (select s.*, row_number() over (order by mark desc) as seqnum
      from student s
      order by mark desc
     ) s
where seqnum = 1;

If you replace row_number() with rank(), you will get all students who have the highest mark.

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

2 Answers2