Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VLZQA-00080z-Bz for pgsql-sql@arkaria.postgresql.org; Mon, 16 Sep 2013 14:04:58 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VLZQ9-0000qV-M4 for pgsql-sql@arkaria.postgresql.org; Mon, 16 Sep 2013 14:04:57 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VLZQ8-0000qO-FN for pgsql-sql@postgresql.org; Mon, 16 Sep 2013 14:04:56 +0000 Received: from smtp.comsquared.com ([209.155.237.140]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VLZQ0-0007wS-SF for pgsql-sql@postgresql.org; Mon, 16 Sep 2013 14:04:55 +0000 Received: from mdpserv.comsquared.com (mdpserv [10.82.40.1]) by smtp.comsquared.com (Switch-3.2.7/Switch-3.2.7) with ESMTP id r8GE4gcH015906; Mon, 16 Sep 2013 10:04:46 -0400 (EDT) Received: from rousee62013 ([10.82.60.155]) by mdpserv.comsquared.com (8.13.6+Sun/8.13.6) with ESMTP id r8GE4eGd006005; Mon, 16 Sep 2013 10:04:40 -0400 (EDT) From: "Edward W. Rouse" To: "'Nathan Mailg'" , References: In-Reply-To: Subject: Re: removing duplicates and using sort Date: Mon, 16 Sep 2013 10:04:40 -0400 Message-ID: <080201ceb2e5$b10e4380$132aca80$@com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0803_01CEB2C4.29FCA380" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: Ac6xvHl0hG9z9sHVQZycKux7Pj+dRQBKRJpQ Content-Language: en-us X-Pg-Spam-Score: 0.1 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. ------=_NextPart_000_0803_01CEB2C4.29FCA380 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Change the order by to order by lastname, firstname, refid, appldate From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@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 BY refid, appldate DESC; I worked on this awhile and is as close as I could get. So this returns rows as you'd expect, except I need to somehow modify this query so it returns the rows ordered by lastname, then firstname. I'm using distinct so I get rid of duplicates in the table where refid (an integer) is used as the common id that ties like records together. In other words, I'm using it to get only the most recent appldate (a date) for each group of refid's that match the lastname, firstname where clause. I just need the rows returned from the query above to be sorted by lastname, then firstname. Hope I explained this well enough. Please let me know if you need more info. Thanks! ------=_NextPart_000_0803_01CEB2C4.29FCA380 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Change the order by to order by lastname, firstname, refid, = appldate

 

From:= = pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@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 BY refid, = appldate DESC;

 

I worked on this awhile = and is as close as I could get. So this returns rows as you'd expect, = except I need to somehow modify this query so it returns the rows = ordered by lastname, then = firstname.

 

I'm using distinct so I = get rid of duplicates in the table where refid (an integer) is used = as the common id that ties like records together. In other words, I'm = using it to get only the most recent appldate (a date) for each = group of refid's that match the lastname, firstname where = clause.

 

I just need the rows = returned from the query above to be sorted by lastname, then = firstname.

 

Hope I explained this = well enough. Please let me know if you need more = info.

 

Thanks!=

------=_NextPart_000_0803_01CEB2C4.29FCA380--