Skip to content
Advertisement

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 …

Advertisement

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).

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement