SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict sdleihssirhc's Avatar
    Join Date
    Feb 2009
    Posts
    387
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Offset with Primary Key

    I have a row's primary key. I would like to select all of the data for both this row and the one right after it. I think I'll be using syntax similar to...

    Code:
    SELECT * FROM `some_table` ORDER BY `some_column` LIMIT x, 2
    But, from what I understand, LIMIT uses offsets. Is there some way to take my primary key and turn it into an offset? Bonus if I don't have to run a second query.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    could you explain "right after it" a little more?

    you want the row with the next higher PK value?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    select * from table where PK >=$yourPK, order by PK Limit 2

    Assuming you mean the next PK whose value is greater than the one you know.

    however what significance does this next record have???

    PK values could be 7,8,9, 19, 45, 82, 83, 90
    i.e. there will be gaps due to deletions. and row with pk 19 has no obvious relationship to the row with pk 45.

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,595
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    If you don't mean the next higher primary key then what ORDER BY are you using to define the order of the records so that next actually has a meaning (the records in the database have no specific order so the next record in the database could be any of the records and could easily change over time)
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SitePoint Addict sdleihssirhc's Avatar
    Join Date
    Feb 2009
    Posts
    387
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yeah, that was terrible wording on my part.

    Dr John's suggestions is exactly what I was looking for. I have an input date with each record, so I ordered everything by that, and then chose "WHERE `id` >= x"

    But that doesn't seem right, now that I've done it and thought about it. The page is set up to display a certain entry, and then (at the bottom of the page) display a link to the next-oldest entry. All I have to start with is the primary key for the current entry (via a GET variable). So, basically, the way I'm doing it now only works if the primary key goes in the same direction as the input date (that is, newer entries also have higher primary key values).

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    next oldest...

    SELECT stuff FROM daTable
    WHERE inputdate < ( SELECT inputdate FROM daTable WHERE id = $current )
    ORDER BY inputdate DESC LIMIT 1
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •