CODEWITHSUNDEEP
mysql
user1545296
user1545296

Reputation: 49

MySQL: Get the max values for specified name

I have a table sometable:

name | math   | history  | status  
-----------------------------
jon  |   80   |   90     | 1  
alex |   90   |   70     | 1  
alex |   87   |   80     | 1  
jon  |   78   |   80     | 0  
alex |   90   |   60     | 1  
jon  |   30   |   100    | 0  
emile|   99   |   89     | 0

what i want to do is get a row that name=alex, status=1 and have the maximal value for math, and then history for rows that satisfied. So the result will be 

alex |   90   |   70     | 1

I try code like:

select * from sometable where (name=$name and status=1) order by math DESC, history DESC

I got what i expect, since i can get the first row only of mysql_fetch_array. But imagine how much the row in mysql_fetch array.

Yes, my code isnt false, but it is not enough "sophisticated" since it fetch many rows,Can anybody give a solution? Thanks. ^^

Upvotes: 0

Views: 65

Answers (1)

zerkms
zerkms

Reputation: 254975

As long as you need only one row - use LIMIT 1 clause:

  SELECT *
    FROM tblname
   WHERE name = 'alex'
     AND status = 1
ORDER BY math DESC
   LIMIT 1

Upvotes: 1

Related Questions