I'm trying to get the latest payments from several clients, but I can't filter them well. I have tried with the following query but it doesn't work, I also tried with subquery but I can't get the desired result. I leave an image of what I get and what I want to get:
select distinct s.nroSocio, s.nombre, s.apellido, max(c.fechaPago)as 'Ultimo pago', c.estado
from Socios s join Cuotas c on s.nroSocio = c.nroSocio
group by s.nroSocio, s.nombre, s.apellido, c.fechaPago, c.estado
order by 4 desc
Here I leave the 2 tables that I use:
This is the query I'm using, try to get the maximum date with a subquery with MAX and TOP, but it doesn't work either. Sure I have a conceptual error, if you could give me a hand it would be of great help.
Your problem stems from being grouped by
fechaPago
although the query was on the right track, when adding toGROUP BY
said field, you lost the result you need, basically removing it you will get the result, something like this:I leave you the example with
DataSet
which I tried, so you can check it.Ejemplo
Based on what you mentioned in the comments, if you wanted to get the state that corresponds to the maximum date, you could do it with a subquery, like so:
I also leave you the example of this query:
Ejemplo
Although he clarified that it can give you a problem in case there are 2 similar dates and with different states, to avoid the error I put the
TOP 1
in the subquery, hopefully it will help you. Greetings.There are two ways to do it, one, and I don't think it works for you, it's a "Having", which is used after the group by and this is used to filter the data and it would look something like this:
The second way would be using a "Where" like this: