Oracle left outer join: howto limit requests in right table

Question

I have a large statement:

SELECT
    a.user_id, a.user_name,
    s.name, s.value,
    d.default
FROM
    accounts a,
    settings s
    LEFT OUTER JOIN default d ON ( d.name = s.name )
WHERE
    s.user_id = a.user_id;

The problem is that settings contains a large amount of entries and I need to pick the one with the highest ID. I can imagine do change the statement and replace the join with a subselect (which grabs the correct entry from settings), but I'm curious to see if there is a better solution. Any input is appreciated. Many thanks!

Answer 1

You can use a sub-query to get just the results you want out of the right table.

Something like:

SELECT
    *
FROM
    accounts a,
    (
      SELECT
          user_id,
          *
      FROM
          settings
      WHERE
          RANK() OVER (ORDER BY id DESC, PARTITION BY user_id) = 1
    ) s

Answer 2

Hi, getting the highest ID from a table could be done with a

select max(id) ...

or with a

select id from settings where rownum=1 order by id desc

(i prefer the first solution) or just like John proposed, but you'll need a subquery anyway. One more thing, there might be some typo in your example, i don't see where d is coming from, neither the point of making an auto left join...

Here is what I would have written

SELECT
    user_id, user_name,
    name, value,
    default
FROM
    accounts join 
    (select user_id,name 
     from settings 
     where RANK() OVER (ORDER BY id DESC, PARTITION BY user_id) = 1) using(user_id)
    LEFT OUTER JOIN default using ( name )

... still subquery