CODEWITHSUNDEEP
mysqldatabase
Krii
Krii

Reputation: 907

How to order MySQL results by another table's count?

I have a database table containing game entries:

--------------------------------------------------------
| game_id | title  | description | entry_time          |
--------------------------------------------------------
| 1       | Game 1 | Descript... | yyyy-mm-dd hh:mm:ss |
--------------------------------------------------------
| 2       | Game 2 | Descript... | yyyy-mm-dd hh:mm:ss |
--------------------------------------------------------

And another containing game play history:

-----------------------------------------------
| game_id | user_id     | entry_time          |
-----------------------------------------------
| 1       | 0da89sadf89 | yyyy-mm-dd hh:mm:ss |
-----------------------------------------------
| 1       | f8jsf89vjs9 | yyyy-mm-dd hh:mm:ss |
-----------------------------------------------
| 2       | f8jsf89vjs9 | yyyy-mm-dd hh:mm:ss |
-----------------------------------------------

I am trying to select results from the first table, based on game popularity.

SELECT games.game_id, games.title, games.description
  FROM `games`
  JOIN foo_db.game_plays game_plays
    ON game_plays.game_id LIKE games.game_id
  WHERE games.title LIKE "%game%"
  ORDER BY COUNT(game_plays.game_id) DESC, games.entry_time DESC
  LIMIT 10

But for some reason, only one result is returned ("Game 1").

When I remove JOIN, and just order the results by entry_time, both results are returned as expected.

Upvotes: 0

Views: 32

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31772

Because MySQL is sometimes really slow when mixing JOIN and GROUP BY, a corelated subquery might be a good alternative:

SELECT games.game_id, games.title, games.description
FROM `games`
WHERE games.title LIKE "%game%"
ORDER BY (
    SELECT COUNT(game_plays.game_id)
    FROM foo_db.game_plays
    WHERE game_plays.game_id = games.game_id
) DESC, games.entry_time DESC
LIMIT 10

Upvotes: 1

Suraj
Suraj

Reputation: 3127

I made this query. Could you please try this.

SELECT a.game_id, a.title, a.description, b.total
  from games a
  JOIN (SELECT game_id, count(user_id) as total from
           game_play group by game_id) b
  ON a.game_id = b.game_id
  AND a.title LIKE '%Game%'
  ORDER BY b.total DESC, a.entry time DESC

OUTPUT

Click Here

Hope it will help.

Upvotes: 1

Related Questions