Order by score then by time

Question

My table structure is below:

Name   email          score time
Hello  abc@gmail.com  100   15
Hello  abc@gmail.com  58    10
Test   def@gmail.com  100   12 
Stack  xyz@gmail.com  90    20
Test   def@gmail.com  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  abc@gmail.com  100   15
Test   def@gmail.com  100   12 
Stack  xyz@gmail.com  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.

may this help to you http://stackoverflow.com/questions/514943/php-mysql-order-by-two-columns — Aman Jain, Oct 16 '15 at 11:41
Recommended : http://stackoverflow.com/questions/5417980/mysql-sql-specific-item-to-be-first-and-then-to-sort-the-rest-of-the-items — Insane Skull, Oct 16 '15 at 11:41

score 3 · Accepted Answer · edited May 23 '17 at 10:27

3

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

edited May 23 '17 at 10:27

Community

1
1

answered Oct 16 '15 at 11:42

Kristian Vitozev

5,791
6
36
56

Thanks dear. Working fine. – Roger Oct 16 '15 at 11:54

score 2 · Answer 2 · answered Oct 16 '15 at 11:42

2

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 ";

answered Oct 16 '15 at 11:42

Abhishek Sharma

6,689
1
14
20

Order by score then by time

2 Answers2