CODEWITHSUNDEEP
mysqlgroup-bysql-order-bymariadb
MaximShatohin
MaximShatohin

Reputation: 21

select with order and group by in maria db(mysql)

i have a dummy question

table game_log

| id | game_id | score| user_id | created_at
|1    | 1      | 100 |  1      | 2015-01-01 00:00:00|
|2    | 1      | 200 |  2      | 2015-01-01 10:00:00|
|3    | 2      | 100 |  2      | 2015-01-01 00:00:00|
|4    | 2      | 500 |  1      | 2015-01-01 01:00:00|
|5    | 2      | 900 |  4      | 2015-01-01 03:00:00|
|X    | 2      | 500 |  1      | 2015-01-02 02:00:00|
|Y    | 3      | 600 |  1      | 2015-01-02 01:00:00|

i need to create a game rating with user ids by date 2015-01-01 result will looks like

| id | game_id | score| user_id | created_at
|2    | 1      | 200 |  2      | 2015-01-01 10:00:00|
|5    | 2      | 900 |  4      | 2015-01-01 03:00:00|

i tried something like 

SELECT   p.id, p.score, p.game_id, p.user_id 
FROM     ( 
                  SELECT   * 
                  FROM     game_log 
                  WHERE    game_log.score>0 
                  AND      game_log.created_at >="2015-01-01 00:00:00" 
                  AND      game_log.created_at <"2015-01-02 00:00:00" 
                  ORDER BY score DESC ) AS p 
GROUP BY p.game_id 
ORDER BY score DESC ;

but get results without order

thank you!

Upvotes: 2

Views: 4171

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31772

MariaDB ignores ORDER BY without LIMIT in subqueries. Why is ORDER BY in a FROM Subquery Ignored?. A workaround would be to use a LIMIT in your subquery, which is big enough not to filter the result.

You could also use a correlated subquery in the WHERE clause with LIMIT 1 to get only the rows with the highest score per game:

SELECT   p.id, p.score, p.game_id, p.user_id 
from game_log p
WHERE p.id = (
    SELECT l.id
    FROM game_log l
    WHERE l.game_id = p.game_id
      AND l.score      >  0 
      AND l.created_at >= "2015-01-01 00:00:00" 
      AND l.created_at <  "2015-01-02 00:00:00" 
    ORDER BY l.score DESC, l.id
    LIMIT 1
)
ORDER BY p.score, p.game_id DESC ;

There are also other solutions, you can find on SO, which will solve your problem.

Upvotes: 4

Related Questions