Re: how to remove a for-loop from programming language and put it into the query?

> (external programming language)
> for ($i = 1; $i < 500; $i++) {
>   // return me the "most recent" diag_value from a hardware_id $i
>   // at the desired timestamp
>   runquery("select diag_value from diagnose_logs where ts <= '2009-12-25
> 23:59:59' and hardware_id = $i order by ts desc limit 1");
> }
>
> can I turn this for-loop into a single query to run in postgres?
>
> Thanks,
> Pedro
>
Try:

SELECT diag_value  FROM diagnose_logs a where id in
 (
     SELECT id FROM diagnose_logs b
       WHERE a.hardware_id=b.hardware_id
    and ts <= '2009-12-25 23:59:59'
    and hardware_id between 1 and 500
       ORDER BY ts LIMIT 1)
     ORDER BY hardware_id;