Correlated Subquery 1

[Quehay]

I'm working on my Oracle SQL skills and trying to get a groups with TOP n records for each group. This works in Access/SQL Server using a correlated subquery that selects top n records ordering by the pertinent field and then selecting based on correlation to a join field in the outer query.

This should work, but I'm getting a "missing right parenthesis" error. What gives?

[tt]select s.site_id,p.pat_id,p.enroll_date
from sites s,patients p
where s.site_id=p.site_id
and p.pat_id in (select pt.pat_id
from patients pt
where pt.site_id=s.site_id and rownum < 4
order by pt.enroll_date);[/tt]

Thanks!


Jeff Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[sem]

Oracle makes sortings after obtaining result set, thus in your case it just should select 4 arbitrary rows and sort them. In general order by clause can not affect result set, thus using it in subquery is meaningless.

Regards, Dima

http://seminihin.narod.ru

 

[Quehay]

Dima,

Thanks for your reply. So how would you accomplish the task of "give me last five sales dates for each salesman" type of querying?

(I checked just to see it in action
[tt] SQL> select 'Top 4' Q,s.inv_lastname N from sites s
2 where rownum < 5 order by 2
3 /

Query Type Last Name
------------ --------------------
Top 4 Brun
Top 4 Jones
Top 4 Lawrence
Top 4 Williams

SQL> select s.inv_lastname N from sites s
2 order by 1
3 /

Last Name
--------------------
Brun
Davis
Jones
Kelly
Lawrence
Morgan
Peters
Rogers
Stevens
Williams
Williams

11 rows selected.
[/tt]


Jeff Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[jimmarkham]

We did this on an Oracle training course 10 years ago. The solution went like this:

Assume that you have a table T1 with a column SALES and you want the top five sales (without using an order by). In Oracle you can do it this way:

select
SALES
from
T1 a
where
5 >
(
select
count(*)
from
T1 b
where
a.SALES < b.SALES
)


Hope this helps

Jim

 

[Quehay]

Thanks Jim!

Hmmm... I don't see how that is processed as set logic yet--do you know the order in which Oracle evaluates this??

You've got a self-join, so the correlated
[tt](select count(*) from T1 b
where a.SALES < b.SALES )[/tt]
part is comparing sales of a table against itself--so this seems like an equality, which would give a count(*) of 0?

I'd really like to understand how this works.

Actually, what I'm looking for is a TOP n grouped by another column. So sales table:

EMP SALE$
Joe 200
Joe 400
Joe 600
Joe 450
Bob 234
Bob 555
Bob 123
Bob 598

Let's say TOP 2 by Salesperson, so the desired results would be:

Joe 600
Joe 450
Bob 555
Bob 598

The original example above should produce this because it grabs a group and then uses the group FK field as parameter for evaluating a TOP n result for the nested query and then repeats for each group.


Jeff Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[jimmarkham]

Jeff

The correlated subquery is evaluated row by row (well... maybe not in some cases but the effect is the same). But the condition is not an '=' its a '<'.

The subquery returns a number (the number of sales values greater than the one we are currently considering) and that number is compared to the constant 5 returning either true or false for the predicate depending on whether its smaller or not. That determines whether or not its in the top five.

You could do what you need by incorporating grouping into both the main query and the subquery to get the top salespersons and then use that to return a list to use in an 'in' predicate.

Alternatively write a function in PL/SQL that works it out for you.

Sorry, I'm at work and I don't have the time for anything more right now.

HTH

Jim

 

[Quehay]

Right, I see it now (it's late here) --I was thinking of it as "select count(*)from table1 a, table1 b where a.sales > b.sales" as a set rather than the per row value of a that's passed for comparison w/ b.

Thanks for unpacking that Jim*. I'm working on PLS tables right now, so that would be a good place to dump a lot of values and do comparisons...

Cheers,

Jeff


Jeff Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[sem]

If you're on Oracle 8i at least you may also consider

select * from (
  select    s.site_id,p.pat_id,p.enroll_date
  from    sites s,patients p
  where     s.site_id=p.site_id
  order by p.pat_id
)
where rownum<=[b]n[/b]

Regards, Dima

http://seminihin.narod.ru