What I want to know is, is it possible (in MYSQL) to know whats the record number for the user_id50?
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.
SELECT COUNT(*) + 1 AS another_record_number
FROM users
WHERE user_id <
( SELECT user_id
FROM users
WHERE firstname = 'Sally'
AND lastname = 'Gratton' )
No, I am not getting any error its just that YOUR count variable is adding +1 to MY count() function:
So for example:
SELECT COUNT(votes.vote_id) as vote_count, users.firstname, users.lastname, COUNT(*) + 1 AS another_record_number
FROM users
LEFT JOIN votes on (votes.user_id = users.user_id)
WHERE users.user_id <
( SELECT user_id
FROM users
WHERE firstname = 'Sally'
AND lastname = 'Gratton' )
GROUP BY votes.user_id
well, if you’re going to just stuff one query inside another like a gross sausage, no wonder
it would appear from your attempt that what you’re really after is the number of votes for each user, and not just for sally gratton, yes?
also, i’m wondering if the ranking shouldn’t also be based on the number of votes rather than the user_id
finally, if indeed you are returning stats for all users rather than just one, you should increment a counter in your application language (php or whatever)to get the ranking while printing out the users one at a time
SELECT COUNT(*) + 1 AS ranking
, ( SELECT COUNT(*)
FROM votes
WHERE user_id = users.user_id ) AS votes
FROM users
WHERE user_id <
( SELECT user_id
FROM users
WHERE firstname = 'Sally'
AND lastname = 'Gratton' )
Why do you want the “record number”? If you already have the user_id, you can ALWAYS retrieve that record by user_id.
If there is some other important parameter by which you want to retreive a record (Sally’s age, for example) you must include that as a field and you can query on that field.