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 
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?


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

SELECT idpersona, Max(idcategoria) as categoria, Max(datapagamento) as pagamento, Max(datascadenza) as scadenza 
FROM personacategoria  
BETWEEN datapagamento 
AND datascadenza 
GROUP BY idpersona
ORDER BY idpersona