0

I have a table completion_log that has team_id, level and completion_time. When each team completes a level, there will be a record inserted with the corresponding level and time values. What is the best SQL query to create a leaderboard from the data - List the teams in descending order of their highest level value and if the highest levels are same, sort by the completion_time in ascending order?

I tried something like:

SELECT team_id, max(level) FROM completion_log GROUP BY team_id ORDER BY max(level)

While this almost does the job, this doesn't sort by completion_time when the values for max(level) for each team are the same. How to do it?

Guruprasad
  • 1,447
  • 4
  • 16
  • 34

1 Answers1

0
SELECT team_id, max(level), completion_time FROM completion_log GROUP BY team_id ORDER BY max(level), completion_time

You can order by more than one field.

Satanicpuppy
  • 1,569
  • 8
  • 11
  • I get this error in PostgreSQL `ERROR: column "completion_log.completion_time" must appear in the GROUP BY clause or be used in an aggregate function` – Guruprasad Feb 04 '13 at 18:26
  • @guru: Hmmm. Wasn't paying attention. If you want to sort by time, you're going to need to add time to the grouping clause, but that will cause it to add an extra row for every time... – Satanicpuppy Feb 04 '13 at 19:59