sql - using AS to avoid same query mySQL

Question

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    npid

            INNER JOIN  players_gameplay_info    pg
            ON  npid.player_id = pg.player_id

        WHERE  npid.node_id = 28
        ORDER BY  pg.score_collected ASC)  AS selectedPlayers
   LIMIT  (SELECT COUNT(*) FROM selectedPlayers) / 2"

There will be approximately 1000 000 entries to count, so I wanted to store it in a variable, however, we cannot store multiple rows in the one like @myLocalVariable

Only one record could be stored. However, it should be possible to interpret the selection AS selectedPlayers

That way I hoped I would re-use the obtained mass of player_ids and iterate over them rather than performing the same selection query just to count half the records

However, I am getting an error

'SQLSTATE[42000]: Syntax error or access violation: 1064 near '(SELECT COUNT(*) FROM selectedPlayers) / 2' at line 10'

Answer 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.