Return only minimum record based on one column in a multiple join

Question

I have a query with a couple of joins, and it's returning two records where I just want it to return one record for each acme_id, the minimum acme_client_id. (Or the minimum acme_client.client_id, I don't care which.) I've tried many different, but I can't get it to work.

SELECT
    acme_client_id,
    acme_client.client_id,
    acme_id
FROM
    acme_client WITH (NOLOCK)
    LEFT JOIN client WITH (NOLOCK) ON acme_client.client_id = client.client_id
    LEFT JOIN acme_client_information WITH (NOLOCK) ON client.acme_client_information_id = acme_client_information.acme_client_information_id
WHERE
    acme_client.acme_decisionmaker_ind = 1
    and acme_client.acme_current_owner_ind = 0
    AND (
        acme_client.participant_start_date IS NULL
        OR acme_client.participant_start_date < GETDATE()
    )
    AND (
        acme_client.participant_end_date IS NULL
        OR acme_client.participant_end_date > GETDATE()
    )
    AND (
        acme_client.acme_client_id IN (
            SELECT
                acme_cooperating_entity_client.acme_client_id
            FROM
                acme_cooperating_entity_client WITH (NOLOCK)
        )
    )
)

Answer 1

Maybe something like this

SELECT min(ac.acme_client_id) min_ac_id,
       min(ac.client_id) min_c_id,
       ac.acme_id
FROM acme_client ac
     join acme_cooperating_entity_client acec on ac.acme_client_id=acec.acme_client_id
     LEFT JOIN client c ON ac.client_id = c.client_id
     LEFT JOIN acme_client_information aci ON c.acme_client_information_id = aci.acme_client_information_id
WHERE ac.acme_decisionmaker_ind = 1 
      and ac.acme_current_owner_ind = 0
      AND (ac.participant_start_date IS NULL
           OR ac.participant_start_date < GETDATE())
      AND (ac.participant_end_date IS NULL
           OR ac.participant_end_date > GETDATE())
group by ac.acme_id;