Search Postgresql Archives

Re: help with a query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



If you do not want to amend your table with extra information, this is
how you do it:

Suppose you have a table

create table events (
 time timestamp,
 object int refers objects(id), -- The thing that had its ignition
turned on or off at this time
 ignition boolean,
 comment varchar
);

You can then do

select distinct on (e1.time, e1.object, e1.comment)
 e1.time as start,
 e2.time as end,
 e2.time - e1.time as duration,
 e1.object,
 e1.comment
from
 events as e1,
 events as e2
where
 e1.object = e2.object
 and e1.time < e2.time
order by
 e1.time, e1.object, e1.comment, e2.time asc;

The trick here is to sort by e2.time in ascending order, and to not
include e2.time (or any of e2:s  fields) in the distinct condition.
This causes all rows where the values from e1 are the same to be
considered duplicates, and only the first one of them included in the
result - which is the one with the lowest e2.time value, since we did
sort on that key in ascending order.

Note that this trick works since order by is processed before the
distinct clause. This is specifically noted in the PostgreSQL manual.

Note that there is a slightly more "intuitive" way of doing this using
a subquery with a min() aggregate function, but this is considerably
less efficient, especially with larger tables (use "explain" and you'll
understund why).



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux