CODEWITHSUNDEEP
sql
Shardendu Mishra
Shardendu Mishra

Reputation: 21

Find the maximum score of each student with subject name

studentid   maths   science art computer sports
1             55     68      59   75     62
2             75     68      79   56     89
3             89     85      74   32     56
4             89     92      86   75     12
5             99     100     45  68      45

How to get query result like this

studentid   maths   science art computer sports

1                                   75  
2                                           89
3             89     
4                     92        
5                     100

and like this

studentid   MaxScore

1
2                                         
3            
4                      
5

Upvotes: 2

Views: 111

Answers (1)

jophab
jophab

Reputation: 5509

Case 1

SELECT studentid, 
       CASE GREATEST(maths,science,art,computer,sports) 
         WHEN maths THEN math
         ELSE ''
       END AS math,
CASE GREATEST(maths,science,art,computer,sports) 
         WHEN science THEN science
         ELSE ''
       END AS science,
CASE GREATEST(maths,science,art,computer,sports) 
         WHEN art THEN art
         ELSE ''
       END AS art,
CASE GREATEST(maths,science,art,computer,sports) 
         WHEN computer THEN computer
         ELSE ''
       END AS computer,
CASE GREATEST(maths,science,art,computer,sports) 
         WHEN sports THEN sports
         ELSE ''
       END AS sports

FROM table_name

Case 2

SELECT studentid, GREATEST(maths,science,art,computer,sports) as MaxScore
FROM table_name

Read about GREATEST() here oracle doc , Mysql

Upvotes: 2

Related Questions