如何查询bls_jobs表中各城市loc_quotient最高的完整记录

Got it, let's figure out how to scale your single-city query to get the highest loc_quotient record for every city in your table. Here are a couple of reliable approaches depending on your database version:

1. Using Window Functions (Modern SQL Databases) #

This is the cleanest and most efficient method for databases that support window functions (like MySQL 8+, PostgreSQL, SQL Server, etc.). We'll use ROW_NUMBER() to rank records within each city group, then pick the top-ranked one.

WITH ranked_jobs AS (
    SELECT 
        *,
        -- Rank rows in each city by loc_quotient (highest first)
        ROW_NUMBER() OVER (PARTITION BY city ORDER BY loc_quotient DESC) AS rank_num
    FROM bls_jobs
)
-- Select only the top-ranked record per city
SELECT city, state, occ_title, jobs_1000, loc_quotient
FROM ranked_jobs
WHERE rank_num = 1;

Notes on ties: #

If multiple records in a city have the same highest loc_quotient, ROW_NUMBER() will arbitrarily pick one. To keep all tied records, replace ROW_NUMBER() with RANK(). If you want consistent ordering for ties (e.g., by job title), add a secondary sort:

RANK() OVER (PARTITION BY city ORDER BY loc_quotient DESC, occ_title ASC) AS rank_num

2. Correlated Subquery (For Older Databases) #

If you're working with an older MySQL version (pre-8.0) that doesn't support window functions, this correlated subquery approach works:

SELECT b1.*
FROM bls_jobs b1
LEFT JOIN bls_jobs b2
    ON b1.city = b2.city 
    AND b1.loc_quotient < b2.loc_quotient
WHERE b2.city IS NULL;

How this works: #

For each record b1, we look for another record b2 in the same city with a higher loc_quotient. If no such b2 exists (hence b2.city IS NULL), b1 is the highest-ranked record for that city. Like the RANK() method, this will return all tied records if there are multiple with the same max loc_quotient.

内容的提问来源于stack exchange，提问作者ekselan