Re: [GENERAL] 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.

[Vincent de Phily]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general