[eng] select from 2 tables last record only

Hello,

I've 2 tables : person and Abonment

I've person.idperson = abonment.idperson.

I've may have 0, one or many abonments for each person.

I'd like, with only ONE QUERY, to have all persons that have no abonment and
any person with the correspondent last abonment (based on the
abonment.IDAbonment, the higer being the last one).
It's very important to get only the last abonment, not a line for every
abonment.

It is possible ?

Bob

Re: [eng] select from 2 tables last record only

Hi Bob

First of all, this is a german NG. Please use a english one in future.

Bob Bedford wrote:
> I've 2 tables : person and Abonment
> I've person.idperson = abonment.idperson.
> I've may have 0, one or many abonments for each person.
> I'd like, with only ONE QUERY, to have all persons that have no abonment and
> any person with the correspondent last abonment (based on the
> abonment.IDAbonment, the higer being the last one).
> It's very important to get only the last abonment, not a line for every
> abonment.
> It is possible ?

Is it? It is! :-)

select p.*, a.*
from person p left join abonment a using (idperson)
where a.idperson is null
union
select * from (
select p.*, a.*
from person p join abonment a using (idperson)
order by a.idabonment desc, p.idperson) a
group by p.idperson;

Not tested... And this does work only, because group by takes the first
one in every group. That isn't documented and can change in future.
HTH, Johannes