How to correctly compose a query with multiple Select and Limit in one query?

Question

MySQL

OK. I'll rephrase the question completely.
I have a growing database and an sql query to it, which should display all records from different tables, but it should recalculate several records in one table with a limit, but, at the same time, the result of the entire query should be with a different limit.

the query itself is:

SELECT 
  playerId, 
  matches, 
  x.kills, 
  x.match_id, 
  x.player_name, 
  x.steam_id_64 
FROM 
  hlstats_PlayerUniqueIds 
  JOIN (
    SELECT 
      count(steam_id_64) as matches, 
      sum(kills) as kills, 
      match_id, 
      player_name, 
      steam_id_64 
    FROM 
      match_player_stats 
    GROUP by 
      steam_id_64 
    ORDER by 
      id DESC
  ) x ON steam_id_64 = CAST(LEFT(hlstats_PlayerUniqueIds.uniqueId,1) AS unsigned) + CAST('76561197960265728' AS unsigned) + CAST(MID(hlstats_PlayerUniqueIds.uniqueId, 3,10)*2 AS unsigned)
GROUP by 
  uniqueId 
ORDER by 
  matches DESC
LIMIT 
  0, 30

how to make JOIN query limited by match_id ?
where each steam_id_64 has exactly 10 last match_ids .
now it calls and sums all entries in match_player_stats , but it should only the last 10 by match_id , i.e. 2000 1999 1998 ... 1990.
if you just add LIMIT 10 to it, it will count the last 10 records in the entire table, and that's not what you need...

mysql database info:
Database client version: libmysql - 5.5. 68-MariaDB || PHP version: 5.4.16

Answer 1

If you need for the last 10 days, then you need to add the necessary WHERE condition to the subquery. LIMIT simply limits the output of a query. Form a request in parts, then put everything together.