SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: Next 50 Records

  1. #1
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Next 50 Records

    How do I grab the next 50 records in a database without an autoincrement value?

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,087
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by bruin03 View Post
    How do I grab the next 50 records in a database without an autoincrement value?
    "Next 50" with respect to what?
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    "Next 50" with respect to what?








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

  4. #4
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    With respect to any random record in the database,
    It can be the 50 after or the 50 before.

    This is for an SEO function i'd like to make. Is this even possible?

    Something tells me the min/max() functions should be able to do this?

    Hm

  5. #5
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does mysql have a row_number function?
    Gosh, I wish the rows were numbered then I'd just use a limit clause at the end of my next select

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bruin03 View Post
    Something tells me the min/max() functions should be able to do this?
    no, they won't help

    what would you take the min/max of? see also post #2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, they won't help

    what would you take the min/max of? see also post #2
    Got it, hence I asked if Mysql had a row_number function to dynamatically assign row indexes?

    Perhaps further explanation is in order... i have a parts database without auto-increment values. EAch time i query a partnumber, I'd also like to query the next 50 or X parts around it.

    For instance if my part # is PX5400, i'd like to make another single query for PX5401 through PX5450 in the same run.

    Does that make more sense? Any advice to how I could accomplish this given the first part# (PX4400). ?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    aha, finally we find something that can be used as a comparison to determine "noxt 50 by what" -- by increasing part number

    so you need
    Code:
    SELECT ...
      FROM ...
    ORDER BY partnumber LIMIT x,y
    where x and y are numbers, e.g. 50,50 to skip over 50 rows and show the next 50
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    aha, finally we find something that can be used as a comparison to determine "noxt 50 by what" -- by increasing part number

    so you need
    Code:
    SELECT ...
      FROM ...
    ORDER BY partnumber LIMIT x,y
    where x and y are numbers, e.g. 50,50 to skip over 50 rows and show the next 50
    Great!
    Is there anywhere to determine X and Y from my current partnumber if there are no indexes?

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Your "could" use GROUP_CONCAT() then explode and search for the index of the selected part. Then splice that array starting at the index and ending 50 items away. Then implode that array and embed it into a WHERE clause using an IN condition against the index. The only problem you would run into is when there aren't 50 item following the current one. In that case you could start from the beginning again if you "must" have 50 items.


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
  •