Oracle sql Inner join first record in right table

Question

my question is this:

I have two tables such as this:

username | portname | symbol | shares
---------+----------+--------+-------
phil     | test     | APL    | 214
---------+----------+--------+--------

It has more records, but that's just an example. Then I have another table such as this, that has multiple records per symbol

symbol | high | low | timestamp
-------+------+-----+-----------
APL    | 200  | 20  | *timestamp object
APL    | 400  | 34  | *timestamp object

I want a table to be returned where I join the two, but only the first row from the second table is joined so something like this is returned:

symbol | high | low | timestamp
-------+------+-----+----------
APL    | 400  | 34  | *timestamp object

So only one record from the right table is matched. I've tried alot of things but haven't gotten anything to work with group by's or distinct.

Thanks!

Answer 1

SELECT t1.symbol, t3.high, t3.low, t3.timestamp 
FROM Table1 t1
JOIN (
      SELECT inn.* 
      FROM (SELECT t2.*, (ROW_NUMBER() OVER(PARTITION BY symbol ORDER BY timestamp DESC)) As Rank 
            FROM Table2 t2) inn 
      WHERE inn.Rank=1
     ) t3
     ON t1.symbol = t3.symbol;  

See SQL Fiddle