title: SQL SELECT order by 2 columns and group by
body: <p>Here are the RS return and the SQL issued, </p>
  
  <pre><code>SELECT *, (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T
   FROM games
  WHERE game_status &gt; 10
  ORDER BY status, T;
  
  
  game_id, player_id, start_time, end_time, score, game_status, is_enabled, T
  65, 22, '2009-09-11 17:50:35', '2009-09-11 18:03:07', 17, 11, 1, 752
  73, 18, '2009-09-11 18:55:07', '2009-09-11 19:09:07', 30, 11, 1, 840
  68, 20, '2009-09-11 18:03:08', '2009-09-11 18:21:52', 48, 11, 1, 1124
  35, 18, '2009-09-11 15:46:05', '2009-09-11 16:25:10', 80, 11, 1, 2345
  13, 8, '2009-09-11 12:33:31', '2009-09-11 15:21:11', 40, 11, 1, 10060
  11, 5, '2009-09-11 12:22:34', '2009-09-11 15:21:42', 55, 11, 1, 10748
  34, 17, '2009-09-11 15:45:43', '2009-09-11 21:00:45', 49, 11, 1, 18902
  2, 1, '2009-09-10 20:46:59', '2009-09-11 23:45:21', 3, 11, 1, 97102
  84, 1, '2009-09-11 23:51:29', '2009-09-11 23:51:42', 10, 12, 1, 13
  </code></pre>
  
  <hr>
  
  <p>I 'd like to group by player_id, (i.e. take the best result each Player_id, it's determined by "game_status - the min", and the time T,</p>
  
  <p>so I added a group by clause, but it doesn't return the min </p>
  
  <pre><code>SELECT *, (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T
   FROM games
  WHERE game_status &gt; 10
  GROUP BY player_id
  ORDER BY game_status, T;
  
  35, 18, '2009-09-11 15:46:05', '2009-09-11 16:25:10', 80, 11, 1, 2345
  13, 8, '2009-09-11 12:33:31', '2009-09-11 15:21:11', 40, 11, 1, 10060
  34, 17, '2009-09-11 15:45:43', '2009-09-11 21:00:45', 49, 11, 1, 18902
  1, 1, '2009-09-10 20:39:44', '2009-09-10 20:41:21', 10, 12, 1, 97
  24, 12, '2009-09-11 14:46:06', '2009-09-11 14:53:30', 10, 12, 1, 444
  5, 3, '2009-09-11 10:56:22', '2009-09-11 11:13:01', 11, 12, 1, 999
  37, 20, '2009-09-11 15:51:13', '2009-09-11 16:15:04', 14, 12, 1, 1431
  79, 31, '2009-09-11 20:34:17', '2009-09-11 20:43:29', 4, 13, 1, 552
  18, 9, '2009-09-11 13:09:47', '2009-09-11 18:33:10', 2, 13, 1, 19403
  72, 30, '2009-09-11 18:46:29', '2009-09-11 18:48:44', 0, 14, 1, 135
  40, 22, '2009-09-11 16:12:39', '2009-09-11 16:18:23', 3, 14, 1, 344
  8, 5, '2009-09-11 12:15:54', '2009-09-11 12:21:48', 25, 14, 1, 354
  85, 33, '2009-09-12 01:14:01', '2009-09-12 01:20:43', 0, 14, 1, 402
  22, 11, '2009-09-11 13:50:41', '2009-09-11 13:57:24', 7, 14, 1, 403
  
  
  SELECT *, min(UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T
   FROM games
  WHERE game_status &gt; 10
  GROUP BY player_id
  ORDER BY game_status, T;
  </code></pre>
  
  <p>If I select min(T), it doesn't return the min row, but the min value on the hold column.</p>
  
  <p>I'd searched for some method with self-join, say, <a href="http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/" rel="nofollow noreferrer">http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/</a></p>
  
  <p>The subquery SELECT for min(), but I can't issue two min() on two columns as it doesn't return the specific rows I want.</p>
  
  <pre><code>select type, min(price) as minprice
  from fruits
  group by type;
  </code></pre>
  
  <p>I hope there's a way as a filter on the first SQL to remove the duplicated player_id rows.</p>