Re: Stranger Results of Select Statements

On Fri, 29 Oct 1999 17:16:56 -0700, "Jingyan" <jingyan.xu_at_tst2000.com> wrote:

>Hi,
>The result of select statement is 6 rows selected.
>
>select name, age, lodging
>from worker w
>where age=(select max(age) from worker
> where w.lodging=lodging);
>
>But, why the result of this a little different select statement is 1 rows
>selected?
>
>select name, age, lodging
>from worker
>where age=(select max(age) from worker w
> where w.lodging=lodging);
>
>Below is table WORKER
>NAME AGE LODGING
>------------------------- --------- ---------------
>BART SARJEANT 22 CRANMER
>ELBERT TALBOT 43 WEITBROCHT
>DONALD ROLLO 16 MATTS
>JED HOPKINS 33 MATTS
>WILLIAM SWING 15 CRANMER
>JOHN PEARSON 27 ROSE HILL
>GEORGE OSCAR 41 ROSE HILL
>KAY AND PALMER WALLBOM ROSE HILL
>PAT LAVAY 21 ROSE HILL
>RICHARD KOCH AND BROTHERS WEITBROCHT
>DICK JONES 18 ROSE HILL
>ADAH TALBOT 23 PAPA KING
>ROLAND BRANDT 35 MATTS
>PETER LAWSON 25 CRANMER
>VICTORIA LYNN 32 MULLERS
>WILFRED LOWELL 67
>HELEN BRANDT 15
>GERHARDT KENTGEN 55 PAPA KING
>ANDREW DYE 29 ROSE HILL
>
>19 rows selected.
>
>Thank you!
>Jingyan
>
>

As you don't make clear which results you expect, I can only demonstrate what happened.
In the second statement's subquery you have an alias w on your table. As by default the subquery starts a new scope, in the expression w.lodging = lodging, the second lodging is referring to the subquery also. This explains why that subquery retrieves only one row, because in fact you have written select max(age) from workers w where 1=1. This also explains why the main query produces one row. In the first statement you have an example of correlated subquery, and it is only by accident your statement works. As soon as there is more than 1 person with the same maximum age per lodging, your statement will fail with 'single row subquery returns more than one row'

Hth,
Sybrand Bakker, Oracle DBA Received on Sat Oct 30 1999 - 03:32:16 CDT