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
Steve TinsleyAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
try this?
select * from
(
SELECT *, @rownum := @rownum + 1 AS rank
FROM Table1, (SELECT @rownum := 0) r
) r
where name = 'bob'
ORDER BY id

Open in new window

1
 
Steve TinsleyAuthor Commented:
Worked first time!! Thanks
0
 
Steve TinsleyAuthor Commented:
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
0
All Courses

From novice to tech pro — start learning today.