Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 6.5 \(1508\))
Subject: Re: removing duplicates and using sort
From: Nathan Mailg <nathanmailg@gmail.com>
In-Reply-To: <1379342569452-5771096.post@n5.nabble.com>
Date: Tue, 17 Sep 2013 12:03:15 -0400
Content-Transfer-Encoding: quoted-printable
Message-Id: <309ABE98-2643-4E6B-B5C9-FF68271B9661@gmail.com>
References: 
 <CAN8Rhd2n84Yxd4QP0SXuyuO+K4f3scQnAYAwq53GDFojOSkAjw@mail.gmail.com>
 <1379342569452-5771096.post@n5.nabble.com>
To: pgsql-sql@postgresql.org
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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 expressi=
ons

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!


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