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!

Solution

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

Comments

[phouse512]

the line "FROM Table1 t1" what is the name of the actual table? is t1 just a naming shortcut?

[Jeffrey Kemp]

@phouse512: docs.oracle.com/cd/B25329_01/doc/appdev.102/b25108/…