Select 1 record based upon 2 conditions in DIFFERENT rows

[neophyte]

Hello All -

I've already got a work around for my question but my work around requires two selects and I'm trying to do this in one go.

question is, is it possible to execute a table select based upon two conditions in different table rows?

What I've got is a table of weather data - eight rows total. each of these 8 rows contains weather forecast data for a 3 hour period of time. each of these rows also contains a Unix time stamp for the beginning of each 3-hour forecast.

So, what I need to do is select the row where the time stamp is equal to or greater than time() AND WHERE the time stamp IN THE NEXT ROW is LESS than time().

I've tried:

SELECT * from tbl_weather_today WHERE fld_epoch >= time() limit 1 - but that gives me the NEXT three hour forecast rather than the current one.

What I really need is something like:

SELECT * from tbl_weather_today WHERE fld_epoch >= time() AND fld_epoch (FOR THE NEXT TABLE ROW) < time();

Is this even possible?

Neophyte

[deejay]

I would think in your query you'd join the table to itself, then your first criteria would refer to the left side of the join, and your second criteria to the right side.

[andrewsmd]

Or you could store the next time in a row also and then every time you add a new record update the previous record with the new entries timestamp.

[WesleyC]

Try this:

SELECT * FROM tbl_weather_today WHERE fld_epoch <= time() ORDER BY fld_epoch DESC LIMIT 1

I'm assuming you want to retrieve the most recent 3-hour forecast. This should select all rows where the forecast start timestamp is less than the current timestamp, which should include the current period. It then sorts the results by timestamp in descending order, meaning the largest timestamp (most recent) is the first record, which the LIMIT 1 should retrieve for you.

[neophyte]

Deejay and andrewsmd -

Thank you both for weighing in with your ideas - much appreciated.

WesleyC -

Have tried your alternate select statement and WHAM! Perfect right out of the box. Thanks so much - now my code for doing the select and various calculations based upon the same are like 12-line leaner! Thanks Wesley!