How to query a table created by another query?

October 16, 2022

So I have a table, users, with user Balances and IDs.
With the below query, I get the table I need – which sorts the users by their balance.

SET @row_num=0; SELECT (@row_num:=@row_num+1) AS serial_num, ID, Balance FROM users ORDER BY Balance DESC; – which returns the following table:

Resulting MYSQL table

How would I find the serial_num of a specific user from the above table by ID?

I’ve tried SELECT * FROM ( the query above ) WHERE ID = "..."; but I must be getting something wrong with the syntax and I don’t quite understand how I would implement a sub-query here.
Cheers

>Solution :

You had actually just 1 like mistake which lead to an uninitialized variable. Replace

SET @row_num=0;

with

SET @row_num:=0;

A little shorter version which can be run in one query would be:

SELECT * 
FROM
(
   SELECT ID, Balance, @row := @row + 1 AS serial_num
   FROM users 
   CROSS JOIN (SELECT @row := 0) r
   ORDER BY Balance DESC
) tmp
WHERE serial_num = 2

SQLFiddle demo