Advanced SQL query problem
 

Hello.
I'm burning my head on a problem with a query.
Querying a payments table, I need to find the actual membership category (let's say: IDCATEGORIA). If I run this:

SELECT idpersona, idcategoria, datapagamento, datascadenza

FROM personacategoria 

WHERE NOW( )

BETWEEN datapagamento

AND datascadenza

ORDER BY idpersona, datascadenza

I get this result:

idPersona  idcategoria  datapagamento  datascadenza

2  2  2008-12-07  2010-03-31

4  2  2007-12-17  2009-03-31

4  1  2008-11-08  2010-03-31

5  2  2008-12-08  2010-03-31

6  2  2007-11-11  2009-03-31

7  2  2008-12-10  2010-03-31

The problem, as you can notice, is the second and third row: person 4 paid on the 2007-12-17 its membership, and then again one year later on the 2008-11-08. So, if I query now (2008-12-17), I obviously am in the overlapping period, so that I retrieve two records.
What I want to do is to retrieve, in cases like this, only the oldest DATASCADENZA record (obviously, for each single IDPERSONA).
I've tried lots of things today, but nothing seems to work... any hint?

thanks,
jj

n.b.: (just in case you need it): primary key for the table is IDPERSONA+IDCATEGORIA+DATAPAGAMENTO

Re: Advanced SQL query problem
 

SELECT idpersona, Max(idcategoria) as categoria, Max(datapagamento) as pagamento, Max(datascadenza) as scadenza 

FROM personacategoria  

WHERE NOW( ) 

BETWEEN datapagamento 

AND datascadenza 

GROUP BY idpersona

ORDER BY idpersona