SQL SELECT order by 2 columns and group by

Question

Here're the RS return and the sql issued,

SELECT *, (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T
 FROM games
WHERE game_status > 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

---

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,

so I added a group by clause, but it doesn't return the min

SELECT *, (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T
 FROM games
WHERE game_status > 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 > 10
GROUP BY player_id
ORDER BY game_status, T;

If I select min(T), it doesn't return the min row, but the min value on the hold column.

I 'd searched for some method with self-join, say, http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

The subquery SELECT for min(), but I can't issue two min() on two columns as it doesn't return the specific rows I wanna.

select type, min(price) as minprice
from fruits
group by type;

I hope there's a way as a filter on the first SQL to remove the duplicated player_id rows. Thanks for any feedback.

Answer 1

It looks like you're missing the MIN function and a slight change to your filtering clause.

As in:

SELECT *, MIN(UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T 
FROM games 
GROUP BY player_id 
HAVING MIN(UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) > 10
ORDER BY game_status, T;

I moved the "> 10" logic because I believe your intent is to filter out those players whose best game status is less than ten. This is a different criteria than filtering out any individual game status entries that are less than ten (which is what you were doing via the WHERE clause).

Try it out. It looks like you're using MySQL, which is not a database system I am all that familiar with.

Answer 2

From what I can gather, you want to see what the minimum time was on the highest game_status for a given player_id, game_id combination. Try this:

select
    g1.game_id,
    g1.player_id,
    min(UNIX_TIMESTAMP(g1.end_time) - UNIX_TIMESTAMP(g1.start_time)) as t,
    g1.game_status
from
    games g1
    inner join (select game_id, player_id, max(game_status) as max_status 
                from games where game_status > 10) g2 on
        g1.game_id = g2.game_id
        and g1.player_id = g2.player_id
        and g1.game_status = g2.max_status
group by
    g1.game_id,
    g1.player_id,
    g1.game_status
order by
    g1.player_id,
    g1.game_id,
    g1.game_status,
    T

Answer 3

I'm a little unsure of some phrases in your question, but you need to do a nested SELECT operation along the following lines:

SELECT g.*
  FROM (SELECT *,
               (UNIX_TIMESTAMP(g.end_time) - UNIX_TIMESTAMP(g.start_time)) AS t
          FROM games
       ) AS g
       JOIN (SELECT player_id,
                    MIN(UNIX_TIMESTAMP(end_time) -
                        UNIX_TIMESTAMP(start_time)) AS min_t
               FROM games
              WHERE game_status > 10
              GROUP BY player_id
             ) AS r
      ON g.player_id = r.player_id AND g.t = r.min_t
ORDER BY game_status, g.t;

The 'r' query returns the player ID and the corresponding minimum time for that player; that is joined with the main table fetching all the rows for that player with the same minimum time. Generally, that will be one entry, but if someone has two games with the same time, the query will return both.

I'm not clear if there's another way of disambiguating the results set; there might be.

Answer 4

Thanks for the replies.

I am looking for Eric and Jonathan 's solution.

Let me explain in detail.

As Eric mentioned, I am seeking for the result from game_status and min time(T), I only need the status > 10 , and ranking from smaller, (i.e. 11 > 12 > 13 > 14, only four status) and determine from their time.

I've taken the top 5 rows of player_id = 18 from the table:

SELECT *, (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T FROM games where player_id = 18 order by game_status, T;

game_id, player_id, start_time, end_time, score, game_status, is_enabled, T
73, 18, '2009-09-11 18:55:07', '2009-09-11 19:09:07', 30, 11, 1, 840
35, 18, '2009-09-11 15:46:05', '2009-09-11 16:25:10', 80, 11, 1, 2345
53, 18, '2009-09-11 16:57:30', '2009-09-11 16:58:28', 0, 14, 1, 58
59, 18, '2009-09-11 17:27:42', '2009-09-11 17:28:51', 0, 14, 1, 69
57, 18, '2009-09-11 17:24:25', '2009-09-11 17:25:41', 0, 14, 1, 76

Player 18 played many times of the game. He got different results(game_status). Now, we are taking the best result on each of the players.

Obviously, the best result for 18 is

73, 18, '2009-09-11 18:55:07', '2009-09-11 19:09:07', 30, 11, 1, 840

As the status is 11, and time is 840.

Note that the best time he took was game_id = 53(Line 3 above), We won't take this result as the status was 14. Hence, use min(UnixTimeSTAMP ... ) won't help as it take 58 as result set.