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

On Mon, Jul 05, 2010 at 11:48:37AM -0300, Pedro Zorzenon Neto wrote:
> 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?

You want to be using DISTINCT ON or some sort of WINDOW function.
DISTINCT ON works with older version of PG, but isn't as standards'
conforming.  The following should do the trick with DISTINCT ON:

  SELECT DISTINCT ON (hardware_id) hardware_id, diag_value, ts
  FROM diagnose_logs
  WHERE ts <= '2009-12-25 23:59:59'
  ORDER BY hardware_id, ts DESC;

You can obviously put in the normal clauses to limit the hardware_ids to
be things you consider important in the normal ways.

--
  Sam  http://samason.me.uk/