Re: ORDER BY problem

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