How do I do a DISTINCT and ORDER BY in PostgreSQL?

Question

PostgreSQL is about to make me punch small animals. I'm doing the following SQL statement for MySQL to get a listing of city/state/countries that are unique.

SELECT DISTINCT city
              , state
              , country 
           FROM events 
          WHERE (city > '') 
            AND (number_id = 123)  
       ORDER BY occured_at ASC

But doing that makes PostgreSQL throw this error:

PGError: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

But if I add occured_at to the SELECT, then it kills of getting back the unique list.

Results using MySQL and first query:

BEDFORD PARK       IL   US
ADDISON         IL  US
HOUSTON         TX  US

Results if I add occured_at to the SELECT:

BEDFORD PARK       IL   US  2009-11-02 19:10:00
BEDFORD PARK       IL   US  2009-11-02 21:40:00
ADDISON         IL  US  2009-11-02 22:37:00
ADDISON         IL  US  2009-11-03 00:22:00
ADDISON         IL  US  2009-11-03 01:35:00
HOUSTON         TX  US  2009-11-03 01:36:00

The first set of results is what I'm ultimately trying to get with PostgreSQL.

Answer 1

Well, how would you expect Postgres to determine which occured_at value to use in creating the sort order?

I don't know Postgres syntax particularly, but you could try:

SELECT DISTINCT city, state, country, MAX(occured_at)
       FROM events 
      WHERE (city > '') AND (number_id = 123) ORDER BY MAX(occured_at) ASC

or

SELECT city, state, country, MAX(occured_at)
       FROM events 
      WHERE (city > '') AND (number_id = 123) 
      GROUP BY city, state, country ORDER BY MAX(occured_at) ASC

That's assuming you want the results ordered by the MOST RECENT occurrence. If you want the first occurrence, change MAX to MIN.

Incidentally, your title asks about GROUP BY, but your syntax specifies DISTINCT.