How do I do a GROUP BY 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.