Re: removing duplicates and using sort

From: Nathan Mailg <nathanmailg(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: removing duplicates and using sort
Date: 2013-09-17 16:03:15
Message-ID: 309ABE98-2643-4E6B-B5C9-FF68271B9661@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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!

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Enrico Oliva 2013-09-19 07:46:51 xmlElement and \n
Previous Message David Johnston 2013-09-16 14:42:49 Re: removing duplicates and using sort