Get the maximum value of a field, from a series of records. MySQL

Question:

The query is as follows, there are 2 tables:

Pagos                             
-idPago                                
-idCliente                             
-fechaEnQuePaga                        
-fechaVencimiento    

Cliente
-idCliente
-nombre
-apellidoPaterno
-apellidoMaterno
-correo

idCliente is a foreign key. Assuming the tables have the following data:

Payment Table:

1 | 1 | 2018-02-23 | 2018-03-23
2 | 2 | 2018-02-23 | 2018-05-23
3 | 2 | 2018-02-23 | 2018-06-23
4 | 1 | 2018-04-23 | 2018-07-23

Customers table:

1 | Daniel | Sosa  | Estrada | sosa@algo.com
2 | Juan   | Casas | Sola    | sola@algo.com

And I want to get the customer data, followed by their payment data but, with the most distant due date, expecting to get:

1 | Daniel | Sosa  | Estrada | 2018-04-23 | 2018-07-23
2 | Juan   | Casas | Sola    | 2018-02-23 | 2018-06-23

I have a query, which is similar but does not give me the expected results:

SELECT
    cliente.idcliente,
    nombre,
    apellidoPaterno,
    apellidoMaterno,
    fechaEnQuePaga,
    fechavencimiento
FROM cliente, pagos
WHERE (cliente.idcliente = pagos.idcliente)
GROUP BY idcliente;

Result:

1 | Daniel | Sosa  | Estrada | 2018-02-23 | 2018-03-25
2 | Juan   | Casas | Sola    | 2018-02-23 | 2018-05-23

I noticed that if I add MAX() it locates the furthest due date, but it does not show its corresponding payment date:

SELECT
    cliente.idcliente,
    nombre,
    apellidoPaterno,
    apellidoMaterno,
    fechaEnQuePaga,
    MAX(fechavencimiento)
FROM cliente, pagos
WHERE (cliente.idcliente = pagos.idcliente)
GROUP BY idcliente;

Result:

1 | Daniel | Sosa  | Estrada | 2018-02-23 | 2018-07-25
2 | Juan   | Casas | Sola    | 2018-02-23 | 2018-06-23

The payment date does not correspond to the one in the row.

Thank you for your answer.

Answer:

Window functions are great for this type of query, but MySQL doesn't have them 🙁

What you can do is a normal join (or a left join if a customer might not have payments), but add a correlative subquery to it that filters the results by MAX(fechavencimiento) :

select c.idcliente,
       c.nombre,
       c.apellidoPaterno,
       c.apellidoMaterno,
       p.fechaEnQuePaga,
       p.fechavencimiento
  from cliente c
  left join pagos p
    on p.idcliente = c.idcliente
   and p.fechavencimiento = (select max(p2.fechavencimiento)
                               from pagos p2
                              where p2.idcliente = p.idcliente)

Warning: If a customer can have duplicate fechavencimiento values ​​in the pagos table, then there is a chance that this query will return more than one record per customer.

Post Views: 9
Scroll to Top