Skip site navigation (1) Skip section navigation (2)

Re: ORDER BY problem

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael Rowan <mike(dot)rowan(at)internode(dot)on(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: ORDER BY problem
Date: 2009-08-15 01:09:26
Message-ID: 2862.1250298566@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Michael Rowan <mike(dot)rowan(at)internode(dot)on(dot)net> writes:
> This query selects the most recent advertisement (in table  
> wha_listing) for all properties (in table wha_property) and returns  
> the sales agency (in table wha_agency), as well as other data.  The  
> query works as expected.

> SELECT DISTINCT ON (property_id)
> property_id, date, wha_property.propnum, wha_street.street_name,  
> wha_suburb.suburb_name, wha_agent.first_name, wha_agent.last_name,  
> wha_agency.name
> FROM wha_listing, wha_agent, wha_agency, wha_property, wha_street,  
> wha_suburb
> WHERE wha_suburb.id = wha_street.suburb_id
> AND wha_street.id = wha_property.street_id
> AND wha_property.id = property_id
> AND wha_agency.id = wha_agent.agency_id
> AND wha_agent.id = agent_id
> AND wha_listing.date >= "2009-01-01" ORDER BY wha_listing.property_id,  
> wha_listing.date DESC;

> I need the resulting list to be sorted by wha_agency.name

> Can I do this by adding something to this query?

You can't mess with that ORDER BY without messing up the associated
DISTINCT ON behavior.  The trick is to put it all in a subselect:

	SELECT * FROM
	  ( ... query as above ... ) ss
	ORDER BY name;

			regards, tom lane

In response to

pgsql-novice by date

Next:From: Jignesh ShahDate: 2009-08-18 10:18:36
Subject: The efficient method to create trigger
Previous:From: Michael RowanDate: 2009-08-15 00:59:43
Subject: ORDER BY problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group