Postgres 10 sql How can I do distinct without all columns

I have this following query that gets the closest cities/town to a certain coordinate that is passed in . It will return a max of 5 records and I only want to do a distinct on city and state . In the database I have some cities repeating if they are large so I would like to do a distinct on that and return a unique set of cities and state . Also some cities like Chicago for instance I have in the database 4 different times but the latitudes and longitude vary slightly that’s why I must exclude latitudes and longitudes from the distinct query . . When i run this query I get the following error

SELECT  distinct on(city::text || ', ' || state::text), latitudes, longitudes
FROM zips 
ORDER BY ABS(28.458414 - latitudes) + ABS(-81.395258-longitudes)
limit 5

ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: SELECT distinct on(city::text || ‘, ‘ || state::text)latitu…

I have also read but can’t seem to get this to work

Sql SELECT DISTINCT – How can I select distinct couple without considering columns’ order? but can’t seem to get this working

Answer

It’s exactly as the error says. Your ORDER BY columns must match the DISTINCT ON columns initially, and you can put any other stuff to order by after that. So:

SELECT  distinct on(city::text || ', ' || state::text) latitudes, longitudes
FROM zips 
ORDER BY city::text || ', ' || state::text, ABS(28.458414 - latitudes) + ABS(-81.395258-longitudes)
limit 5

I don’t see why you’d concatenate those fields though in the DISTINCT ON, it doesn’t appear to serve any purpose since it will be as “unique” as specifying the columns individually, which would be more performant. So:

SELECT  distinct on(city, state) latitudes, longitudes
FROM zips 
ORDER BY city, state, ABS(28.458414 - latitudes) + ABS(-81.395258-longitudes)
limit 5

Bear in mind that specifying columns in DISTINCT ON does not return those columns, so your query currently would only return latitudes, longitudes, without any city or state info.