[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] removing duplicates and using sort
From:       Nathan Mailg <nathanmailg () gmail ! com>
Date:       2013-09-17 16:03:15
Message-ID: 309ABE98-2643-4E6B-B5C9-FF68271B9661 () gmail ! com
[Download RAW message or body]

Yes, that's correct, modifying the original ORDER BY gives:

ORDER BY lastname, firstname, refid, appldate DESC;
ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Using WITH works great:

WITH distinct_query AS (
    SELECT DISTINCT ON (refid) id, refid, lastname, firstname, appldate
        FROM appl WHERE lastname ILIKE 'Williamson%' AND firstname ILIKE 'd%'
        GROUP BY refid, id, lastname, firstname, appldate
        ORDER BY refid, appldate DESC
    )
SELECT * FROM distinct_query ORDER BY lastname, firstname;

Thank you!



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[prev in list] [next in list] [prev in thread] [next in thread]