Using AS To Avoid Same Query MySQL

I am trying to get the IDs of half of the good players, but only those contained in node_id=28 ' SELECT * FROM (SELECT npid.player_id FROM node_to_player_ids

Solution 1:

https://dev.mysql.com/doc/refman/5.7/en/select.html says:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

• Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.
• Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

In other words, LIMIT does not allow its argument to be a column name, subquery, or expression.

You'll have to do this in two queries. The first to get the count you want:

SELECT FLOOR(COUNT(*)/2) AS player_count FROM selectedPlayers;

The second for your original query, with LIMIT applied, using a literal numeric argument or query parameter:

SELECT npid.player_id  
FROM node_to_player_ids AS npid
INNER JOIN  players_gameplay_info AS pg
  ON npid.player_id = pg.player_id
WHERE npid.node_id = 28
ORDER BY pg.score_collected ASC
LIMIT <player_count>

Where I wrote <player_count> you would replace that with the result from the first query, either by interpolating the integer value, or by using a session variable or query parameter. Or if you're writing this query in a stored procedure you can DECLARE a local variable.

If you're using phpMyAdmin, note that session variables do not survive between requests, because each request starts a new session.

Solution 2:

declare @linecount int
with query as 
        (SELECT npid.player_id 
         FROM node_to_player_ids npid
            INNER JOIN  players_gameplay_info    pg
            ON  npid.player_id = pg.player_id
        WHERE  npid.node_id = 28) 
select @linecount = count(*)/2 from query
set rowcount @linecount
select * from query

?