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, firs
Note that you could always do something like:
WITH original_query AS (
SELECT DISTINCT ...
)
SELECT *
FROM original_query
ORDER BY lastname, firstname;
OR
SELECT * FROM (
SELECT DISTINCT
) sub_query
ORDER BY lastname, firstname
I am thinking you cannot alter the existing ORDER BY other
Change the order by to order by lastname, firstname, refid, appldate
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
On Behalf Of Nathan Mailg
Sent: Saturday, September 14, 2013 10:36 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] removing duplicates and using sort
I'm using 8.4.17 and I have the following query working, but it's not quite
what I need:
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
