I've got a table and want to outer-join another table, getting only the first row (the one with lowest nr) of the second table using Oracle 10g.
Edit: nr is unique within an id
Table x Table y
id id nr code
1 1 1 B
2 1 2 A
3 2 2 A
Expected result:
id nr code
1 1 B
2 2 A
3 NULL NULL
Example with test data (does not do the limitation to single row but should allow faster testing):
WITH
x AS( SELECT 1 id FROM dual UNION SELECT 2 FROM dual UNION SELECT 3 FROM dual ),
y AS( SELECT 1 id, 1 nr, 'B' code FROM dual
UNION SELECT 1, 2, 'A' FROM dual
UNION SELECT 2, 2, 'A' FROM dual
) -- end of test data
SELECT x.id, y.nr, y.code
FROM x
LEFT OUTER JOIN y ON ( y.id = x.id )