how to find the highest value in mysql table

i have a mysql table i.e

st_id | name | email | maths | chemistry | bio | social_study
1     | john |开发者_运维知识库@a.com | 20    |  23       | 10  |  15

my question is how can i find the highest subject score, the second last and so on

Note that all the subject fields have int(11) values

Break your database into 3 tables like:

Students:

st_id | name | email  
1     | john |@a.com  

Courses:

cr_id | name  
1     | maths  
2     | chemistry  
3     | bio  
4     | social_studies

StudentCourses:

st_id | cr_id | score  
1     | 1     | 20   
1     | 2     | 23   
1     | 3     | 10   
1     | 4     | 15  

Now you can do:

SELECT s.name, MAX(sc.score) FROM Students s INNER JOIN StudentCourses sc ON s.st_id = sc.st_id;

SELECT * FROM <table>
ORDER BY <field> DESC
LIMIT <needed number of rows>

Example:

SELECT * FROM <table>
ORDER BY maths+chemistry+bio+social_study DESC
LIMIT 3

Strictly PHP method: I assume you want to maintain association with field names. In that case, just use asort($row); on each row in your query result, assuming you fetched the row as an array. asort will sort the array from lowest value to highest (with additional flags to tweak the results if needed), while maintaining keys. A foreach loop will then allow you to work with each key/value pair in the sorted order.

st_id | name | email | maths | chemistry | bio | social_study

1     | john |@a.com | 20    |  23       | 10  |  15

The query can be for top marks

SELECT id,GREATEST(mark,mark1,mark2) AS `top`  FROM `students`