Re: SELECT, DATETIME and checking result counts

[Christian Page]

Hi,

The table itself has many fields (around 60).
For the latest hour, my SELECT query is:

select DATETIME,LAT,LON,IC_ICONE,IC_SIZX,IC_SIZY,IC_PRI,IC_VAL,IC_ALIGN,IC_DEPX,IC_DEPY from data where DATETIME BETWEEN DATE_SUB(NOW(), INTERVAL MINUTE(NOW()) MINUTE) and NOW();

and the only (inefficient I think) way I see is to make one query per hour:
select DATETIME,LAT,LON,IC_ICONE,IC_SIZX,IC_SIZY,IC_PRI,IC_VAL,IC_ALIGN,IC_DEPX,IC_DEPY from data where DATETIME BETWEEN DATE_SUB(NOW(), INTERVAL (MINUTE(NOW())+60) MINUTE) and DATE_SUB(NOW(), INTERVAL (MINUTE(NOW())+1) MINUTE);

and process the output using php or bourne shell externally to MySQL to see if there is any record matching the select requests for each hour, and go back more hours if some hours have no record.
I am looking for a more efficient way to combine all these queries and have MySQL select only the 5 last active hours having at least one record valid.

I could have, by example, records with these DATETIME values:
DATETIME 200503281230
DATETIME 200503281205
DATETIME 200503281030
DATETIME 200503281010
DATETIME 200503280903
DATETIME 200503280900
DATETIME 200503280740
DATETIME 200503280630
DATETIME 200503280610
DATETIME 200503280530

I would want MySQL to return me record from hours: 12, 10, 09, 07, 06, and sort them in time, the latest one at the top. Currently I would know how to do it if I would want MySQL to return records from hours 12 to 08 with one select.