CODEWITHSUNDEEP
phpsql
Roger
Roger

Reputation: 238

Order by score then by time

My table structure is below:

Name   email          score time
Hello  [email protected]  100   15
Hello  [email protected]  58    10
Test   [email protected]  100   12 
Stack  [email protected]  90    20
Test   [email protected]  50    40

Using select query

$q="SELECT name, MAX(score) as score ,email FROM users GROUP BY email ORDER BY MAX(score) DESC LIMIT 10";

Below result generates.

Name   email          score time
Hello  [email protected]  100   15
Test   [email protected]  100   12 
Stack  [email protected]  90    20

What I am confused is, if two user have same score, I want to sort the result based on lowest time, since "Test" user scored 100 in 12 seconds, it should come first.

Upvotes: 3

Views: 1159

Answers (2)

Kristian Vitozev
Kristian Vitozev

Reputation: 5971

SELECT name, MAX(score) as score, email 
FROM users GROUP BY email 
ORDER BY MAX(score) DESC, time ASC LIMIT 10; # note how multiple column ordering is made

Read more: SQL multiple column ordering

Upvotes: 3

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Try this mysql query 

  $q="SELECT name, MAX(score) as score 
       ,email FROM users GROUP BY email 
      ORDER BY MAX(score) DESC,time LIMIT 10 ";

Upvotes: 2

Related Questions