Link to home
Start Free TrialLog in
Avatar of Steve Tinsley
Steve TinsleyFlag for United Kingdom of Great Britain and Northern Ireland

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
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Steve Tinsley

ASKER

Worked first time!! Thanks
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