1

I have a table which holds the userID, highscore, and a timestamp.

I would like to sort by timestamp, so that the newest entries are at the top, and also, don't show any userID twice in the list, ergo, they need to be grouped.

I also need it to be sorted by score.

$sql="SELECT * FROM $table GROUP BY userID  ORDER BY time DESC";

$sql2="SELECT * FROM $table GROUP BY userID ORDER BY score DESC";

At this moment, I see that Group by is done first, so I probably just get some random timestamps/stamps. I need to order them first, them Group them. However, I get errors if I switch Group by and Order by.

Could anybody help me out here? Thanks.

Runar
  • 245
  • 3
  • 17
  • possible duplicate of [SQL multiple column ordering](http://stackoverflow.com/questions/2051162/sql-multiple-column-ordering) – david strachan Oct 27 '14 at 20:33

1 Answers1

1

GROUPing is always done before ORDERing. To order records before grouping, there are 2 approaches:

  1. ORDER BY in subquery and then apply GROUP BY. This is not usually a better approach since running a subquery is very expensive, especially if you have many records. For small number of records, this is going to be fine.

    $sql="SELECT * FROM (SELECT * FROM $table ORDER BY time DESC) GROUP BY userID";

  2. A better solution is to get the MAX(time) in a subquery and then join that to your table on both the userID and the max time.

    SELECT p1.* FROM $table t1 INNER JOIN ( SELECT max(time) MaxTime, userID FROM $table GROUP BY userID ) t2 ON t1.userID = t2.userID AND t1.time = t2.MaxTime order by t1.time DESC;

I have written them from top of my head.

HTH

Harsh Gupta
  • 4,348
  • 2
  • 25
  • 30