Web Analytics Made Easy -
StatCounter Select the next greatest value - CodingForum

Announcement

Collapse
No announcement yet.

Select the next greatest value

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new 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
    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.
    Be yourself. No one else is as qualified.

    Comment

    Working...
    X