How to query a table created by another query?-CodePudding

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

CodePudding user response：

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