Obtain the maximum value of a field, from a series of records. MYSQL

The window functions are ideal for this type of query, but MySQL does not have them: (

What you can do is a normal join (or a left join if a client may not have payments), but add a correlating subquery 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 fechavencimiento values in table pagos , then there is a possibility that this query will return more than one record per client.

You could do the following:

SELECT
    C.idcliente,
    C.nombre,
    C.apellidoPaterno,
    C.apellidoMaterno,
    P.fechaEnQuePaga,
    P.fechavencimiento,
FROM
    cliente C
    LEFT JOIN (
        SELECT
            P2.idCliente,
            P2.fechaEnQuePaga,
            P2.fechaVencimiento
        FROM
            pagos P2
            INNER JOIN (
                SELECT
                    idCliente,
                    MAX(fechaVencimiento) AS fechaVencimiento
                FROM pagos
                GROUP BY idCliente
            ) P1 ON P1.idCliente = P2.idCliente AND P1.fechaVencimiento = P2.fechaVencimiento
    ) P ON P.idCliente = c.idCliente

Let's see:

• First of all with the innermost subquery whose alias is P1 we get the maximum fechaVencimiento per client
• Then with the subquery P2 we obtain the record (s) whose payments correspond to the maximum fechaVencimiento of P1 . Important , if you had several records for a client with the same fechaVencimiento (in your example it seems that it does not happen) you should add another nesting to "decide" with which row you stay, provided that you wait to have a single line per customer.
• Finally we make the JOIN final% between clientes and pagos , use a LEFT if you do not want to lose sight of those clients that do not have payments or a INNER if you want to eliminate them from the query.

This is a pretty basic solution using plain SQL, I imagine that in MySql there are better ways to solve it.

Use INNER JOIN with ORDER BY

SELECT c.idCliente,c.nombre,c.apellidoPaterno,c.apellidoMaterno,p.fechaEnQuePaga,p.fechaVencimiento
  FROM Pagos p
INNER JOIN Clientes c ON p.idCliente = c.idCliente
ORDER BY p.fechaVencimiento DESC

In this way you will be able to obtain the union of your rows and you will be able to filter by the field that you want in the order that you specify (ASC (ascending), DESC (descending))

The query that resembles it is because when you group it takes the last value of the group, so you just have to add a GROUP BY,

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

If this does not work, change the ASC to the DESC and try again.

You have to be careful with this, if you import it into a mysql that has the constraint ONLY_FULL_GROUP_BY it will mark error

As well they mention this would be with inner join like this

 SELECT 
   cliente.idcliente, 
   nombre, 
   apellidoPaterno, 
   apellidoMaterno,
   fechaEnQuePaga,
   fechavencimiento,
FROM cliente
INNER JOIN pagos
ON cliente.idcliente=pagos.idcliente
GROUP BY idcliente
ORDER BY fechavencimiento ASC;