I have a (simplified) table looking like:
serial int4
custid int4
stamp datetime
I want, for each customer, to get the serial of the the
highest timestamp for that customer, and for duplicate
timestamps, the highest serial. The way I thought how to
do that would be:
select custid, serial
from test b
where serial =
(select serial
from test a
where a.custid = b.custid
order by serial desc limit 1);
but thats not allowed. The best I could do is:
select custid, max(serial)
from test
where (custid,stamp) in
(select custid, max(stamp)
from test
group by custid)
group by custid;
Which is ugly and only works in this case because
max does what I want. It has a horrible plan though:
Aggregate (cost=1.36 rows=11 width=8)
-> Group (cost=1.36 rows=11 width=8)
-> Sort (cost=1.36 rows=11 width=8)
-> Seq Scan on test (cost=1.36 rows=11 width=8)
SubPlan
-> Aggregate (cost=1.36 rows=11 width=12)
-> Group (cost=1.36 rows=11 width=12)
-> Sort (cost=1.36 rows=11 width=12)
-> Seq Scan on test (cost=1.36
rows=11 width=12)
In the more general case, this won't work.
Is there a better way of doing this? Does postgres 7.0 do
better in this case?
--
Martijn van Oosterhout <kleptog@cupid.suninternet.com>
http://cupid.suninternet.com/~kleptog/
