SQL Query for max value from subsets

I have a table bls_jobs with the following columns: city, state, occ_title, jobs_1000, and loc_quotient

I am trying to retrieve the highest loc_quotient for each city (each city has several occ_titles, and each occ_title has a loc_quotient)

Currently, I can use this query:

SELECT *
FROM bls_jobs
WHERE city = 'Hattiesburg'
ORDER BY loc_quotient DESC
LIMIT 1

Which does return what I’m looking for (the highest loc_quotient in the city, with each of the columns returned), but I’m struggling to figure out how to have it do this for all cities so I have a returned output of just each city’s highest loc_quotient along with it’s data from the other columns …

Answer

Use distinct on:

SELECT DISTINCT ON (j.city) j.*
FROM bls_jobs j
ORDER BY j.city, j.loc_quotient DESC;

DISTINCT ON is a convenient Postgres extension. It returns the first row in each group, where groups are the keys in the DISTINCT ON () clause (and the ORDER BY is consistent with them).