How can I use Oracle's CONNECT BY PRIOR to only get specific revisions of hierarchical records? I've got a table schema in Oracle 11 which is storing records that are linked together. The table schema is roughly like this

CREATE TABLE "SOMEDATA" (
    "ID" INTEGER NOT NULL,
    "ITEMNAME" VARCHAR2 NOT NULL,
    "LINKLEFT" VARCHAR2 DEFAULT NULL,
    "LINKRIGHT" VARCHAR2 DEFAULT NULL,
    "CREATE_TIMESTAMP" TIMESTAMP NOT NULL,
    "VALUE1" VARCHAR2 DEFAULT NULL,
    "VALUE1" VARCHAR2 DEFAULT NULL
)


The purpose of this table is to store timestamped records which may link to other records, similar to a hierarchy. My challenge is build a query that at any given specific timestamp, and for a given itemname, recursively fetch the latest versions of the rows that chain to the right of the item. E.g. in the following data:

+----+----------+----------+-----------+---------------------+------------+----------+--+
| Id | Itemname | Linkleft | Linkright |  create_timestamp   |   value1   |  value2  |  |
+----+----------+----------+-----------+---------------------+------------+----------+--+
|  1 | item1    | (null)   | item2     | 23/08/2014 00:00:00 | Test       | Data     |  |
|  2 | item2    | item1    | item3     | 23/08/2014 00:01:25 | Some       | More     |  |
|  3 | item3    | item2    | (null)    | 23/08/2014 00:05:00 | Even       | MoreData |  |
|  4 | item2    | item1    | item3     | 24/08/2014 00:00:00 | Changed    | Data     |  |
|  5 | item1    | (null)   | item2     | 25/08/2014 12:37:02 | New        | Better   |  |
|  6 | item3    | item2    | item4     | 27/08/2014 00:00:00 | Lipsum     | Lopsum   |  |
|  7 | item4    | item3    | (null)    | 27/08/2014 00:01:00 | Additional | Stuff    |  |
+----+----------+----------+-----------+---------------------+------------+----------+--+


For the query ITEMNAME='item1' and CREATE_TIMESTAMP=23/08/2014 14:58:59, I need to return records #1,2 and 3. This is because these are the latest linked records that link to the latest record for 'item1' at that particular point in time. However, for the query ITEMNAME='item1' and CREATE_TIMESTAMP=01/01/2015 12:00:00, I need to return records #5,4,6 and 7, because at that time, these records were the latest versions.

I've been playing about with Oracle's CONNECT BY PRIOR to build something like

SELECT * FROM "SOMEDATA" START WITH "ITEMNAME"="item1" CONNECT BY PRIOR "LINKRIGHT"="RIC"


This obviously chains the records together, but pays no attention to timestamps and returns all values for rows which match.

It seems that hierarchical records is a nicely solved problem, but not in the case where you want to also store the historical values for records in the hierarchy and query for any time. Is this sort of thing possible in this table structure, or am I heading down the wrong track entirely?
how about:

with preresult as (
SELECT id,itemname,
   linkleft,
   linkright,
   create_timestamp,
   value1,
   value2,
   ROW_NUMBER ()
      OVER (PARTITION BY itemname ORDER BY create_timestamp DESC)
      rownumber
   FROM somedata
   WHERE create_timestamp &lt; :timestamp)  
select * from preresult where rownumber &lt; 2


and enter as :timestamp the desired date and time

hth,
macfly.