views:

2541

answers:

2

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 )
+7  A: 
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  *
FROM    (
        SELECT  x.id, y.nr, y.code, ROW_NUMBER() OVER (PARTITION BY x.id ORDER BY y.nr) AS rn
        FROM    x
        LEFT OUTER JOIN y
        ON   y.id = x.id
)
WHERE rn = 1
Quassnoi
The DESC in PARTITION BY should be removed to get the first row, right?
Peter Lang
For some reason I thought you need a highest Y.NR :) Sure, remove the DESC if you need the lowest
Quassnoi
Just what I needed, thanks.
Adam Backstrom
A: 

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 ) where rownum=1

@CoolMagma: Welcome to Stackoverflow! Please use preview before posting, and try to format code using leading spaces or the "Code Samples" button.
Peter Lang