Filter duplicated value in SQL

Question

I'm trying to build a query that provides me a list of five jobs for a weekly promotion. The query works fine and gives the right result. There is only one factor that needs a filter.

We want to promote different jobs of different companies. The ORDER BY makes it possible to select jobs with the highest need for applicants. It could be that one company has five times the most urgent need. Therefore the query selects the five jobs of one company. I want to add a filter so the query selects a maximum of two or three job from one company. But couldn't find out how.

I've tried it with different angles of the DISTINCT function. But without results. I think that the underlying problem has something to do with a wrong group function on job.id (just a thought) but can't find a solution.

SELECT 
    job.id, 
    company_name, 
    city, 
    job.title, 
    hourly_rate_amount, 
    created_at, 
    count(work_intent.id), 
    number_of_contractors,
    (count(work_intent.id)/number_of_contractors) AS applicants,
    (3959 * acos(cos(radians(52.370216)) * cos( radians(address.latitude)) 
        * cos(radians(longitude) - radians(4.895168)) + sin(radians(52.370216)) * sin(radians(latitude)))) AS distance 
FROM job
    INNER JOIN client on job.client_id = client.id
    INNER JOIN address on job.address_id = address.id
    LEFT JOIN work_intent on job.id = work_intent.job_id
    INNER JOIN job_title on job.job_title_id = job_title.id
WHERE job_title.id = ANY
    (SELECT job_title.id FROM job_title WHERE job.job_title_id = '28'
        or job.job_title_id = '30'
        or job.job_title_id = '31'
        or job.job_title_id = '32'
    )
    AND job.status = 'open'
    AND convert(job.starts_at, date) = '2019-09-19'
    AND hourly_rate_amount > 1500
GROUP BY job.id
HAVING distance < 20
ORDER BY applicants, distance
LIMIT 5

I expect the output would be:

job.id - company_name - applicants
14842  - company_1    - 0
46983  - company_6    - 0
45110  - company_5    - 0
95625  - company_1    - 1
12055  - company_3    - 2

Please prefix each column with table (or table alias) it belongs to. It maybe be clear for you but definitively not for us. — The Impaler, Sep 20 '19 at 15:04
Learn to use proper `GROUP BY`. It would probably solve your problem. — Eric, Sep 23 '19 at 16:41
Follow the `groupwise` tag for ways to get N from each group. That becomes a subquery to the final query. — Rick James, Oct 05 '19 at 01:46

score 1 · Answer 1 · answered Sep 20 '19 at 18:02

One quite simple solution, that can be applied without essentially modifyin the logic of the query, is to wrap the query and use ROW_NUMBER() to rank the records. Then, you can filter on the row number to limit the number of records per company.

Consider:

SELECT *
FROM (
    SELECT 
        x.*,
        row_number() over(partition by company order by applicants, distance) rn
    FROM (
        -- your query, without ORDER BY and LIMIT
    ) x
) y 
WHERE rn <= 3
ORDER BY applicants, distance
LIMIT 5

Filter duplicated value in SQL

1 Answers1