Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts

    Question MySQL: Getting the record number?

    Hi

    Suppose I have a query:

    Code:
    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
    Last edited by phantom007; 04-09-2012 at 09:36 AM.

  • #2
    Regular Coder
    Join Date
    Jun 2002
    Posts
    905
    Thanks
    23
    Thanked 5 Times in 5 Posts
    Hi Cancer10,

    Bit confused.. surely the record number for "user_id 50" would be 50? You can call the record number directly rather than using the wild card..

    SELECT user.user_id AS record_number FROM users ORDER BY user_id DESC
    ..but I suspect that's not what you are after. Can you explain a little deeper, I'm sure we can work it out for you.

    Cheers,

  • Users who have thanked sir pannels for this post:

    phantom007 (04-09-2012)

  • #3
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts
    Hi sir pannels

    Thanks for replying.

    The record number of user_id 50 may/may not be 50 because, if some users are deleted in between then there will be a problem.

    I should be able to query for a record by first/last name and get their record # as well.

  • #4
    Regular Coder
    Join Date
    Jun 2002
    Posts
    905
    Thanks
    23
    Thanked 5 Times in 5 Posts
    Can you print your table schema/structure here so that I can take a look, from that I should be able to tell how how to get the record number.

    Cheers,

  • Users who have thanked sir pannels for this post:

    phantom007 (04-09-2012)

  • #5
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,852
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • Users who have thanked abduraooft for this post:

    phantom007 (04-09-2012)

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Posts
    905
    Thanks
    23
    Thanked 5 Times in 5 Posts
    That stackoverflow thread suggests creating a COUNT of rows in the result set, which can change depending on the query.

    It may be worth explaining exactly what your doing and why you need the record number, there may be a better solution.

    Cheers,

  • Users who have thanked sir pannels for this post:

    phantom007 (04-09-2012)

  • #7
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts
    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:

    Code:
    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
    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

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Okay, I'm really confused.

    If you only want to show, say, the first 10 in ranking, then why wouldn't you just do
    Code:
    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 
    LIMIT 10
    and then do the counting in PHP, as you suggested?

    Or is it that you want to be able to say, when Sally logs in, "Congratulations, you are in 3rd place"????

    If that is the case, then you don't want that simple-minded query, in the first place!

    When Sally logs in, surely one of the things you do is go grab her USER_ID from the users table and store it in a session variable, no?

    SO:
    Code:
    $sql = "SELECT 1 + COUNT(*) AS placement FROM (
               SELECT user_id, COUNT(*) AS vcount FROM votes GROUP BY user_id
               HAVING vcount > ( SELECT COUNT(*) AS uvotes FROM votes WHERE user_id = $userid )
           ) AS V";
    And now the value of placement tells you exactly where Sally (or $userid) stands.

    EDIT: Tested it, even. It works.
    Last edited by Old Pedant; 04-09-2012 at 09:38 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (04-10-2012)

  • #9
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts
    Thanks Old Pedant

    I will give this a try and will get back to you if I have any query.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Or tell us what you really WANT instead of what doesn't work.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •