On 5 July 2010 16:26, Pedro Zorzenon Neto <pedro2009@xxxxxxxxxxxxx> wrote: >> I'm probably misunderstanding the problem, but can't you just do: >> >> SELECT >> diag_value >> FROM >> diagnose_logs >> WHERE >> ts <= '2009-12-25 23:59:59' >> AND >> hardware_id BETWEEN 1 AND 500 >> ORDER BY >> ts DESC >> LIMIT 1 > > Hi Thom, > > Yes, I think you misunderstood. > > An example of a table: > > hardware_id | ts | diag_value > 1 | 2009-12-25 14:00:00 | 43.5 (*) > 1 | 2009-12-26 15:00:00 | 43.6 > 1 | 2009-12-24 13:00:00 | 43.7 > 2 | 2009-12-24 15:00:00 | 43.8 (*) > 2 | 2009-12-24 14:00:00 | 43.9 > 2 | 2009-12-24 14:16:00 | 43.9 > 2 | 2009-12-27 14:00:00 | 44.0 > > I need to get the "most recent" value before "2009-12-25 23:59:59" from > every hardware_id. > > For hardware_id=1, the value would be: > 1 | 2009-12-25 14:00:00 | 43.5 > for hardware_id=2, the value would be: > 2 | 2009-12-24 15:00:00 | 43.8 > > I need a query that will return me those lines marked with (*) :-) is > this possible? > > Thanks! > > D'oh! I completely ignored that "LIMIT 1". okay... what Sam said. Thom -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
