MySQL query, MAX() + GROUP BY
(Tested in PostgreSQL 9.something)
Identify the rid and timestamp.
select rid, max(timestamp) as tsfrom testgroup by rid;1 2011-04-14 18:46:002 2011-04-14 14:59:00Join to it.
select test.pid, test.cost, test.timestamp, test.ridfrom testinner join (select rid, max(timestamp) as ts from test group by rid) maxton (test.rid = maxt.rid and test.timestamp = maxt.ts)
select *from ( select `pid`, `timestamp`, `cost`, `rid` from theTable order by `timestamp` desc) as mynewtablegroup by mynewtable.`rid`order by mynewtable.`timestamp`Hope I helped !
SELECT t.pid, t.cost, to.timestamp, t.ridFROM test as tJOIN ( SELECT rid, max(tempstamp) AS maxtimestamp FROM test GROUP BY rid) AS tmax ON t.pid = tmax.pid and t.timestamp = tmax.maxtimestamp