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.
- id
- users
- 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 exists 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.
Thanks Everyone for your help.
Here is the Updated Link If anyone want to get such kind of results:
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.