MySQL: Getting the record number?

Databases
1

Hi

Suppose I have a query:


SELECT * FROM users ORDER BY user_id DESC

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

2

yes, just count the number of rows with a lower user_id and add 1

SELECT COUNT(*) + 1 AS record_number_user_50
  FROM users
 WHERE user_id < 50
3

Hi

Thanks for your reply.

Question: why are you using WHERE user_id < 50

What if I want to know whats the record number where the user’s first name = “Sally” and Last name = “Gratton”?

Thanks

4

because that’s what you asked for :slight_smile:

SELECT COUNT(*) + 1 AS another_record_number
  FROM users
 WHERE user_id <
       ( SELECT user_id
           FROM users
          WHERE firstname = 'Sally'
            AND lastname = 'Gratton' )
5

HI

THanks so much for your kind help.

The query run fine but does not work when there is already a count() function in the select query. Is there a work around for this?

Thanks

6

i have no idea what you’re doing here

please show the query and the error message

7

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

I am getting the result as:

vote_count | firstname | lastname | another_record_number
-----------------------------------------------------------------------------
10         | Sally      | Gratton   | 11
8

well, if you’re going to just stuff one query inside another like a gross sausage, no wonder :slight_smile:

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

helps?

:slight_smile:

9

Well, what I want is the count of the Votes for sally gratton as well as her ranking in the list. These two are NOT related in anyway.

I just want the stats of sally gratton and not all users which is why I have added the WHERE clause in my query.

10

so don’t put them in the same SELECT clause :slight_smile:

ranking based on what?

11

ranking is nothing but just the record # on which sally gratton falls in the user’s table.

12

so that’s ranking by user_id, right?

13

yes.

14 
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' )
15

Thanks

I will try this out and get back to your if I have any queries.

16

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.