From: | Vincent de Phily <vincent(dot)dephily(at)mobile-devices(dot)fr> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Reid Thompson <jreidthompson(at)gmail(dot)com> |
Subject: | Re: Re: Need help with what I think is likely a simple query - for each distinct val, return only one record with the most recent date. |
Date: | 2011-09-13 10:34:01 |
Message-ID: | 12543744.Hr9E4lqlnk@moltowork |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Monday 12 September 2011 22:51:54 Reid Thompson wrote:
> test=# select distinct on (val1) val1, val2, val3 from (SELECT
> max(val3) OVER (PARTITION BY val1), * FROM sampledata) as sq where val3
> = max order by val1;
Other things I've tried (was limited to PG8.3 so no "OVER (PARTITION...)"
support) :
SELECT DISTINCT ON (val1), val2, val3 FROM table ORDER BY val1, val3;
SELECT val1, val2, val3 FROM table WHERE id IN (
SELECT sq.i FROM (
SELECT val1, max(val3) FROM table GROUP by 1
) AS sq (v,i))
My case was a bit different since I wanted the record for distinct(A,B)
instead of just DISTINC(A), and since I had a primary key available on the
table. But let it be food for thought.
However, none of those queries are either efficient or beautiful, so I ended
up populating a "last_values" table via a trigger, which is way more efficient
if it fits your needs :
CREATE TABLE last_values (PRIMARY KEY foo, bar integer, baz integer);
CREATE OR REPLACE FUNCTION insert_last_value() RETURNS TRIGGER AS $$
BEGIN
UPDATE last_values SET val1=NEW.val1... WHERE ...;
IF NOT found THEN
BEGIN
INSERT INTO last_values (...) VALUES (NEW....);
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
UPDATE last_values SET ... WHERE ...;
END;
END IF;
RETURN NULL;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER last_values_trigger AFTER INSERT ON values FOR EACH ROW EXECUTE
PROCEDURE insert_last_values();
--
Vincent de Phily
From | Date | Subject | |
---|---|---|---|
Next Message | Venkat Balaji | 2011-09-13 11:12:52 | Re: Has Pg 9.1.0 been released today? |
Previous Message | Raymond O'Donnell | 2011-09-13 10:16:15 | Re: pg_dump |