MySQL: Getting the record number?

Hi

Suppose I have a query:

SELECT * FROM users ORDER BY user_id DESC

Code (markup):

and this returns 300 rows.

What I want to know is, is it possible (in MYSQL) to know whats the record number for the user_id 50?


I know I can run a loop in my php script and start a counter and get the record number with simple if() condition, but if there are 8000 records in my table, I dont want to iterate thru all the 8000 records...hence looking for an efficient SQL Query.


Pls Help
Thanks

 

SELECT @curRow := @curRow + 1 AS row_number, a.* 
FROM users a
JOIN (SELECT @curRow := 0) b
ORDER BY user_id DESC

Code (markup):

 

ok here is the situation:

I have 2 tables. VOTES and USERS.

The VOTES table contains the votes for each users.
The USERS table contains the user information.

Now, with my query:

SELECT COUNT(votes.vote_id) as vote_count, users.firstname, users.lastname
FROM users
LEFT JOIN votes on (votes.user_id = users.user_id)
GROUP BY votes.user_id
ORDER BY vote_count

Code (markup):

I can get the list of users and the count of votes each user has got in DESC order.


vote_count | firstname | lastname |
-----------------------------------------------------------------------------
10 | Peter | Gratton
7 | Joe | Gratton
5 | Sally | Gratton
2 | Mathew | Gratton


So, what I want is a query that will tell me the record number of a user whose first name is "Sally" so that I can show my website users that the user "Sally" ranks 3 among all users (since Sally is in record #3).


Hope I am clear this time

 

mooj99 posted the solution
that will create a variable

$row['row_number']

Code (markup):

for all the records. using the code above you could display the users rank (row number). However it would start with 0, so you would need to set the "@curRow := 0" in the JOIN to 1