SQL SELECT is ordered by 2 columns and grouped by
Here is the RS and SQL output released,
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 like to group the player_id (ie take the best score for each Player_id, which is defined by "game_status - min" and the time T,
so i added a group by clause but it doesn't return 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 does not return the minimum row but the min value in the hold column.
I was looking for some self-joining method, say http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
SELECT subquery for min (), but I cannot execute two min () on two columns as it does not return the specific rows that I want.
select type, min(price) as minprice
from fruits
group by type;
I hope there is a way as a filter on the first SQL to remove duplicate player_id rows.
From what I can gather, you want to know what is the minimum time it was the highest game_status
for this combination player_id
, game_id
. Try the following:
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
source to share
It looks like you are missing the MIN function and a small change in your filtering article.
How 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 logic "> 10" because I think your goal is to filter out those players whose best game status is less than ten. This is a different criterion than filtering out individual game state records that are less than ten (which you did with the WHERE clause).
Try it. It looks like you are using MySQL which is not a database system. I know everything.
source to share
I'm a little unsure about some of the phrases in your question, but you need to do a nested SELECT operation on 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;
Request 'r' returns player ID and corresponding minimum time for this player; which joins to the main table fetching all rows for that player with the same minimum time. Typically this will be one entry, but if someone has two games at the same time, the query will return both.
I don't understand if there is another way to disambiguate the result set; may be.
source to share
Thanks for answers.
I'm looking for Eric and Jonathan's solution.
Let me explain in detail.
As Eric mentioned, I am looking for the result from game_status and min time (T), I only need statuses> 10, and the rating from the lower one, (i.e. 11> 12> 13> 14, only four statuses) and determine their time.
I took the top 5 rows 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 has played many times in the game. It has different results (game_status). We now take the best score for 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 state is 11 and the time is 840.
Note that the best time he took was game_id = 53 (line 3 above). We will not accept this result since the status was 14. Therefore, using min (UnixTimeSTAMP ...) will not help 58 as a result.