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. - Mailing list 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