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 ...
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).
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加