...

View Full Version : Select the next greatest value



treeleaf20
08-24-2011, 05:30 PM
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:


$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.

Old Pedant
08-24-2011, 09:08 PM
A few different ways:



SELECT MIN(zip_code) FROM zips where zip_code NOT IN ( SELECT MIN(zip_code) FROM zips )

or


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


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


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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum