SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cancer10 View Post
    What I want to know is, is it possible (in MYSQL) to know whats the record number for the user_id 50?
    yes, just count the number of rows with a lower user_id and add 1
    Code:
    SELECT COUNT(*) + 1 AS record_number_user_50
      FROM users
     WHERE user_id < 50
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cancer10 View Post
    Question: why are you using WHERE user_id < 50
    because that's what you asked for

    Quote Originally Posted by cancer10 View Post
    What if I want to know whats the record number where the user's first name = "Sally" and Last name = "Gratton"?
    Code:
    SELECT COUNT(*) + 1 AS another_record_number
      FROM users
     WHERE user_id <
           ( SELECT user_id
               FROM users
              WHERE firstname = 'Sally'
                AND lastname = 'Gratton' )
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cancer10 View Post
    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?
    i have no idea what you're doing here

    please show the query and the error message
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    No, I am not getting any error its just that YOUR count variable is adding +1 to MY count() function:

    So for example:

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

    Code:
    vote_count | firstname | lastname | another_record_number
    -----------------------------------------------------------------------------
    10         | Sally      | Gratton   | 11

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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

    helps?

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cancer10 View Post
    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.
    so don't put them in the same SELECT clause

    ranking based on what?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    ranking is nothing but just the record # on which sally gratton falls in the user's table.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cancer10 View Post
    ranking is nothing but just the record # on which sally gratton falls in the user's table.
    so that's ranking by user_id, right?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    yes.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    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' )
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks

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

  16. #16
    Avid Logophile silver trophy
    ParkinT's Avatar
    Join Date
    May 2006
    Location
    Central Florida
    Posts
    2,283
    Mentioned
    181 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by cancer10 View Post
    What I want to know is, is it possible (in MYSQL) to know whats the record number for the user_id 50?
    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.
    Don't be yourself. Be someone a little nicer. -Mignon McLaughlin, journalist and author (1913-1983)


    Literally, the best app for readers.
    Make Your P@ssw0rd Secure
    Leveraging SubDomains


Bookmarks

Posting Permissions

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