I hope everyone of you is in good health.

I am working on a project in which I need some help regarding fetching the position of some user based on some criteria. I am showing my query with some example database and table.

For example, I have a database in which there is a table named users.

In the users, there are 3 fields.

1. id
2. users
3. status

I am using the following query to get the row-position of all user if their status=1 using the following query:

SELECT
    @a:=@a 1 as serial_no, username
FROM users
    JOIN (SELECT @a:= 0) a
WHERE status=1;

But, I want to get the position of specific user using username field. e.g:username="user3".

But when I run the below query, It returns me that this username exist on 1st position as the serial_no.

SELECT
    @a:=@a 1 as serial_no, username
FROM users
    JOIN (SELECT @a:= 0) a
WHERE status=1 AND username="user3";

I have created the below SQL Fiddle, so you can help me in an easier way.

https://dbfiddle.uk/QsXF0mBh

Thanks

CodePudding user response：

If you want to know the row number, you need to postpone filtering the rows until after they have been numbered:

SELECT *
FROM (
    SELECT
        @a:=@a 1 as serial_no, username
    FROM users
        JOIN (SELECT @a:= 0) a
    WHERE status=1
) AS t
WHERE t.username='user3';

The same will be true if you try to use the ROW_NUMBER() window function.

Note this creates a temporary table for the rows matching in the subquery.