query that selects only the most recent record

[bookor]

I have a persons table with a personid. As the individuals addresses, phone numbers etc. change new records are added to the address, phone number tables etc, linked by personid. The addresses, phones tables have sequence numbers to indicate the most recent entry. There must be a simple sql statement to select name from persons and the address with the highest seq number from the address or phone table. it would be nice if the database designer had provided an active field but they didnt and I dont have the option of changing the table structure.<br>
<br>
ive tried all kinds of variations of selects like where person.id=address.id and seqnum=greatest(seqnum)<br>
<br>
but I cant get the right combination.<br>
<br>
any suggestions would be appreciated...<br>
<br>
<br>
<br>

 

[calahans]

The function to use is max(seqnum), then just join to your original table. <br>
<br>
If you are still having trouble let me know, I'm a bit busy now!<br>
<br>
C

 

[MapMan]

Bookor,<br>
<br>
Have you tried setting an index in the address details table to be decending order on the date added field or primary key field?<br>
<br>
HTH,<br>
<br>
MapMan

 

[kalyan]

If you know the person id for whom you are going to pick up data then you can you this peice of PL/SQL <br>
<br>
Declare<br>
Cursor cr_pid (vc_person In persons.Personid%type) is<br>
select address, name<br>
from persons<br>
where personid = vc_person<br>
order by seqnum DESC;<br>
rec_pid cr_pid%rowtype;<br>
Begin<br>
-- Cursor will pickup first record and closes it. so<br>
-- the record type variable contains the latest<br>
-- name and address of the person<br>
open cr_pid ('K1');<br>
fetch cr_pid Into rec_pid;<br>
close cr_pid;<br>
End;<br>
<br>
Cheers<br>
Kalyan

 

[connectxyzzy]

Select personid,etc<br>
from persons outer<br>
where seqnum=(select max(seqnum) <br>
from persons inner<br>
where inner.personid=outer.personid)<br>
<br>
This correlated subquery may be slooooo

http://wwwww

if the table is quite large.....<br>