Re: ORDER BY problem

Michael Rowan <mike.rowan@internode.on.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