0

Hello – I am trying to construct an Oracle 11g query that will find the latest version of an entity by going through a table that has a history of moves. An example of this is that the table could contain a list of addresses that a person has lived at and different addresses that they have moved to.

For example, you might live at ADDRESS_ID 123 but then moved to ADDRESS_ID 456 and moved again to ADDRESS_ID 789.

It is also possible that you lived at ADDRESS_ID 123 the whole time and never moved therefore you would never appear on the MOVE_LIST table.

The goal of the query would be so if I select ADDRESS_ID 123 in the first example above then it would tell me the MOST RECENT ADDRESS_ID that the person is at (789).

The table is called MOVE_LIST and has the following columns: MOVE_LIST_ID ORIGINAL_ADDRESS_ID DESTINATION_ADDRESS_ID

The query I have so far doesn’t complete this task since it doesn’t go through the list of moves:

Select DESTINATION_ADDRESS_ID
from MOVE_LIST 
where ORIGINAL_ADDRESS_ID = '123'

Any tips on this query would be GREATLY appreciated.

Here is some sample data:

MOVED_LIST_ID   ORIGINAL_ADDRESS_ID DESTINATION_ADDRESS_ID
1                123                456
2                456                789

Thank you

Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
AAA
  • 2,388
  • 9
  • 32
  • 47
  • What's the table and structure that contains the address if they didn't move? and hwo do you determine "LATEST" the highest original_address_ID ? and how do the addresses relate back to the person? We need table structures here and sample data to be able to offer help. Best I can do is say it's something like...http://stackoverflow.com/questions/189213/sql-selecting-rows-by-most-recent-date or http://stackoverflow.com/questions/10786087/how-to-get-the-latest-employee-record-in-oracle – xQbert Jul 30 '14 at 18:24
  • If they never moved then they wouldn't be in the table. In this case I would be happy to get no results. I am adding sample data to the original question. Thank you! – AAA Jul 30 '14 at 18:28
  • It would be helpful if you provided sample of data `move_list` contains. if you were using Oracle 12c, the `move_list` table would be a good candidate for applying temporal validity feature to. – Nick Krasnov Jul 30 '14 at 18:28
  • Thank you Nicholas - I added sample data but we are using 11g – AAA Jul 30 '14 at 18:30
  • 1
    ah ok so this is a hierarchical table. connect by prior should give you the desired results once you get to a the final leaf node. Similar to: http://stackoverflow.com/questions/21671568/get-all-last-level-children-leafs-from-a-node-hierarhical-queries-oracle-11g – xQbert Jul 30 '14 at 18:36

1 Answers1

3

In you case data in the move_table form a hierarchy. So, in order to find out the last address a person moved to, a simple hierarchical query can be used:

with move_list(moved_list_id, original_address_id, destination_address_id) as(
  select 1, 123, 456 from dual union all
  select 2, 456, 789 from dual
)
select destination_address_id
  from move_list
 where connect_by_isleaf = 1    
 start with original_address_id = 123
 connect by original_address_id = prior destination_address_id

Result:

DESTINATION_ADDRESS_ID
----------------------
                   789
Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78