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