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 2 of 2
  1. #1
    Regular Coder
    Join Date
    Oct 2009
    Posts
    438
    Thanks
    9
    Thanked 7 Times in 7 Posts

    Select the next greatest value

    All,
    Say I have the following in my database as INT values:

    1004
    1013
    1020

    I wrote a query to get the Min value so it looks like this:
    PHP Code:
    $qry "Select MIN(zip_code) from zips"
    That works fine, however now I want to write a query that says my current value is 1004, now I want to select the next highest value of 1013. How can I do that?

    Thanks in advance.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    A few different ways:

    Code:
    SELECT MIN(zip_code) FROM zips where zip_code NOT IN ( SELECT MIN(zip_code) FROM zips )
    or
    Code:
    SELECT MIN(Z.zip_code) 
    FROM zips AS Z, 
         ( SELECT MIN(zip_code) AS minzip
           FROM zips ) AS M
    WHERE Z.zip_code <> M.minzip
    or
    Code:
    SELECT Z.zip_code
    FROM zips AS Z,
         ( SELECT MIN(zip_code) AS minzip
           FROM zips ) AS M
    WHERE Z.zip_code <> M.minzip
    ORDER BY Z.zip_code
    LIMIT 1
    or
    Code:
    SELECT M.zip_code
    FROM ( SELECT zip_code FROM zips ORDER BY zip_code LIMIT 2 ) AS M
    ORDER BY M.zip_code DESC LIMIT 1
    Or variations on those themes.
    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
    •