Steve Tinsley
asked on
MYSQL Record Number
I want to be able to get the record number within my query.
I have a simple table
ID NAME
11 steve
82 bob
32 peter
I can get the rank number of the query like this:
SELECT *, @rownum := @rownum + 1 AS rank
FROM table, (SELECT @rownum := 0) r
ORDER BY id
returns:
ID NAME rank
11 steve 1
82 bob 2
32 peter 3
BUT if I run a query like this:
SELECT *, @rownum := @rownum + 1 AS rank
FROM table, (SELECT @rownum := 0) r
WHERE name = "bob"
ORDER BY id
Its returns:
ID NAME rank
82 bob 1
But I want it to still return rank = 2.... the position in the db.
Can anyone help.
Steve
I have a simple table
ID NAME
11 steve
82 bob
32 peter
I can get the rank number of the query like this:
SELECT *, @rownum := @rownum + 1 AS rank
FROM table, (SELECT @rownum := 0) r
ORDER BY id
returns:
ID NAME rank
11 steve 1
82 bob 2
32 peter 3
BUT if I run a query like this:
SELECT *, @rownum := @rownum + 1 AS rank
FROM table, (SELECT @rownum := 0) r
WHERE name = "bob"
ORDER BY id
Its returns:
ID NAME rank
82 bob 1
But I want it to still return rank = 2.... the position in the db.
Can anyone help.
Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Ryan,
I am suddenly getting issues with this query. It is giving me the number plus 1.
I dont completely understand how this works.
Any ideas??
Steve
I am suddenly getting issues with this query. It is giving me the number plus 1.
I dont completely understand how this works.
Any ideas??
Steve
ASKER