CODEWITHSUNDEEP
phpmysql
souce code
souce code

Reputation: 675

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

Upvotes: 1

Views: 5193

Answers (4)

Harish
Harish

Reputation: 2324

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`

Upvotes: 0

bob-the-destroyer
bob-the-destroyer

Reputation: 3154

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.

Upvotes: 0

aroth
aroth

Reputation: 54856

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;

Upvotes: 5

Delagen
Delagen

Reputation: 25

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

Upvotes: 1

Related Questions