How to filter data using min(date_field) - postgres

Question

This is my query to fetch list of applicants, I'm joining applicants with another table certificationsapplicants, from there i need to find min(lic_exp_date) a field in certificationsapplicants

SELECT cnctr.applicant AS applicant_id, cnctr.status, cnctr.hired_date, cnctr.systemuser, EXTRACT(DAY FROM now() - cnctr.hired_date) as probation_date,
apl.first_name, apl.last_name, apl.email, apl.address1, apl.city, applicant_state, apl.phone_number,
sysrole.name as role_name,
crtapl.lic_exp_date
FROM contractors cnctr
JOIN applicants apl ON apl.id = cnctr.applicant
JOIN contractorsrole crole ON crole.applicant_id = cnctr.applicant JOIN systemnurserole sysrole ON sysrole.id = crole.role_id
JOIN certificationsapplicants crtapl ON crtapl.applicant_id = cnctr.applicant JOIN certificationtypes crttype ON crttype.id = crtapl.certification_id
where cnctr.status = 44 

LIMIT 25 OFFSET 0

What i need is I need to find the min(crtapl.lic_exp_date), each applicants will have multiple certificates, I need to find the certificate that expires first. I tried to query it directly, and it worked but it's not working when I joins applicants with other tables.

The error is: must appear in the GROUP BY clause or be used in an aggregate function

This query worked:

select min(cpl.lic_exp_date), cpl.applicant_id from certificationsapplicants cpl group by cpl.applicant_id

Could you guys please help.

score 2 · Accepted Answer · answered Mar 24 '20 at 06:52

This should do the trick (note: untested code)

SELECT
    cnctr.applicant AS applicant_id,
    cnctr.status,
    cnctr.hired_date,
    cnctr.systemuser,
    EXTRACT(DAY FROM now() - cnctr.hired_date) as probation_date,
    apl.first_name,
    apl.last_name,
    apl.email,
    apl.address1,
    apl.city,
    applicant_state,
    apl.phone_number,
    sysrole.name as role_name,
    crtapl.lic_exp_date
FROM contractors cnctr
JOIN applicants apl ON apl.id = cnctr.applicant
JOIN contractorsrole crole ON crole.applicant_id = cnctr.applicant
JOIN systemnurserole sysrole ON sysrole.id = crole.role_id
JOIN certificationtypes crttype ON crttype.id = crtapl.certification_id
JOIN (
    SELECT
        MIN(cpl.lic_exp_date) AS lic_exp_date,
        cpl.applicant_id
    FROM certificationsapplicants cpl
    GROUP BY cpl.applicant_id
) crtapl ON crtapl.applicant_id = apl.id
WHERE cnctr.status = 44 

LIMIT 25 OFFSET 0

This is a common issue that has been answered a lot on here. Here are a couple of good posts with explanations if you want more detail.

How to filter data using min(date_field) - postgres

1 Answers1