I wanted to mount a inner join that would only bring the values filtered by a minimum value field
select min(data_vencimento) from pagamento where cod_situacao = 3,
cli.cpf, cli.nome, con.data_inicio, con.data_fim,
sit.situacao, con.valor_mensalidade from cliente cli
inner join contrato con on con.cpf = cli.cpf
inner join pagamento pag on pag.COD_CONTRATO = con.COD_CONTRATO
inner join situacao sit on sit.cod = pag.cod_situacao;
You should do this:
SELECT MIN(co.data_vencimento), c.cpf
FROM cliente c
INNER JOIN contrato co ON co.cpf = c.cpf
INNER JOIN pagamento p ON p.COD_CONTRATO = co.COD_CONTRATO
INNER JOIN situacao s ON s.cod = p.cod_situacao
WHERE cod_situacao = 3
GROUP BY c.cpf
Taking into account that the field "date_vending" exists in the table "contract" and that you want the lowest due date per customer.
If you want this grouped by contract, it should look like this:
SELECT MIN(co.data_vencimento), con.cod
FROM cliente c
INNER JOIN contrato co ON co.cpf = c.cpf
INNER JOIN pagamento p ON p.COD_CONTRATO = co.COD_CONTRATO
INNER JOIN situacao s ON s.cod = p.cod_situacao
WHERE cod_situacao = 3
GROUP BY co.cod
The important thing here is to understand that group by along with the aggregation function "MIN" is what will give you this information.
Abs!