Re: Get 10 latest customers

In article <3p2lvu$ibr_at_portal.gmu.edu>, mtran2_at_osf1.gmu.edu (My-Phuong L Tran) writes:

>Although this a slick solution, there may be problems of
>interpretation in the context of the relational model. One way to
>see this (I think) is try the substitution of
>
>(1) "...10 >= (select..." by
>
>(2) "...10 = (select..."
>
>The second select statement only returns 1 row, *not* 10. It seems
>that the interpretation of the select statement is dependent on the
>algorithm that Oracle uses in interpreting general select statements, i.e. the
>subquery typically being independent of the main query. Does anyone
>know the 'correct' interpretation of select statements in which there are
>dependencies between main queries and sub queries?

Conceptually, the 10 >= query is a SQL formation of "select all rows from the table where there are 10 or fewer rows with a greater transaction date," which effectively selects the 10 most recent dates, in situations where the date values are unique. By using an = sign, as in (2), you are simply saying "select all rows from the table where there are EXACTLY ten rows with a greater transaction date," which, of course, would return only one row (with unique date values.)

I don't follow your references to algorithms & interpretations "in the context of the relational model," because the interpretation by the database is standard and predictable, and certainly relationally sound. The relationship is known as a "correlated subquery," indicated by the fact that the child query references a column from the parent. In other words, the subquery is evaluated for each row of the parent query, as you might expect.

Michael Stowe
Constellation Engineering
http://www.mcs.net/~stowe/ Received on Sat May 13 1995 - 00:00:00 CEST