CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
sahira shamsu
sahira shamsu

Reputation: 47

Getting student name with highest total mark in SQL

I have table named 'marks' which stores marks of students.

     id name      social math   science
     2  Max Ruin    85   85      56 
     3  Arnold      75   40      170
     4  Krish       60   70      50
     5  John        60   90      80

I want to get the name of student who has highest total mark.

I tried query like this

 SELECT name, (social + math +science) AS total_mark FROM marks where total_mark = max(total_mark );

But this is not working. Can anyone please help ?

Upvotes: 2

Views: 4336

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If you want one student, then you can do this without a subquery:

select max(name) keep (dense_rank first order by (social + math + science) desc) as top_name
from marks;

Upvotes: 0

Chrᴉz remembers Monica
Chrᴉz remembers Monica

Reputation: 1904

select social + math + science as total, marks.*
from marks
order by social + math + science desc
fetch first 1 rows only;

Unfortuantely, sqlfiddle didnt work with fetch first 1 rows only. My previous answer produced a wrong result.

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

get the max() total first then use a subquery 

    select * from marks where (social+math+science) =    
      (select max(social+math+science) from marks);

Upvotes: 6

Vinay Kaklotar
Vinay Kaklotar

Reputation: 424

You can try this

 SELECT *,(SUM(social)+SUM(math)+SUM(science)) AS total_mark FROM `marks` GROUP BY id ORDER BY total_mark DESC LIMIT 1

Upvotes: 0

Venkataraman R
Venkataraman R

Reputation: 12959

You can use Rank() function to get the rank of the persons.

SELECT *
FROM
(SELECT name, (social + math + science) as total_mark, Rank() OVER (ORDER BY  (social + math + science) DESC) AS rnk
FROM marks)t
WHERE rnk = 1;

Upvotes: 0

Related Questions