Oracle left outer join: howto limit requests in right table
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 imagi开发者_运维问答ne 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!
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
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
精彩评论