Re: Simple SQL question.

From: Ian A. MacGregor <ian_at_tethys.SLAC.Stanford.EDU>
Date: Thu, 1 Jul 1993 15:57:19 GMT
Message-ID: <C9HtnK.Ats_at_unixhub.SLAC.Stanford.EDU>

In article <1993Jun30.174838.27674_at_rcvie.co.at>, nm_andli_at_rcvie.co.at (Paul Andlinger) writes:
|> In article <486_at_ecsa01.encon.pge.com> gnm1_at_encon.pge.com (Gary Mercuri) writes:
|> >Try the following script as a formula:
|> >
|> >
|> >SQL> l
|> > 1 select last_name from employee emp
|> > 2 where 5 > (select count(*) from employee where last_name < emp.last_name)
|> > 3* order by last_name
|> >
|> >Best Wishes,
|> >
|> >Gary Mercuri
|> >
|> >-----------------------------------------------------------------------------------
|> >
|> >Chris G. vanHasselt (chrisvh_at_med.unc.edu) wrote:
|> >: My question has either a real simple solution or it can't be done! Is
|> >: there a way, without creating some sort of dummy field, to write a SQL
|> >: query so that instead of returning all records satisfying a where clause,
|> >: it returns only the first 5 records satisfying that clause.
 

|> >: Is there an easy way to do this?
|> >
|> >
|> >: Please reply to chrisvh_at_med.unc.edu
 

|> >: Thanks in advance
|> >: Chris van Hasselt
 

|> >: chrisvh_at_med.unc.edu
|>
|>
|> Gary's formula looks good but unfortunately doesn't work. It requires a presorted table what does not confirm to relational theory.
|> The ORDER BY clause only sorts the SELECTED rows for output.
|>
|> Paul Andlinger
|> Alcatel Austria Research Center

I have not been following this thread closely, but it is NOT! true that Gary's method requires a presorted table. To prove this I'll use the familiar emp table and return the 5 "ename(s)" which are first alphabetically. The statement to do this is basically the same as Gary wrote:

                 Select ename from emp a
                 where  5 > (select count(empno) from emp b
                             where a.ename > b.ename)
                 order by ename;

I've used the primary key, empno, instead of "*" ,because it is more efficient to do so. The query returns

ENAME

---

ADAMS
ALLEN
BLAKE
CLARK
FORD and yet select ename from emp returns

ENAME

---

SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS ENAME

---

JAMES
FORD
MILLER showing the table is not presorted.

The reason why this works has to do with processing a correlated subquery. The subquery is run for each row of the outer query. The database will first test the subquery against "SMITH" since "SMITH" is > 13 of the other enames and 13 is not > 5, the predicate resolves to false and SMITH is not returned. Next "ALLEN" is tested. "Allen" is only greater than one other ename, and 5 is > 1; therefore the predicate is true, and Allen is returned. All rows are processed in a similar way, yielding the 5 names which are first alphabetically in the table. The sort condition is then applied to the 5 rows returned by the subquery to display them in proper sequence.

Note if there were two employees by the name of FORD, then the query would return 6 rows,

                           Ian MacGregor
                           Stanford Linear Accelerator Center
                           (415) 926-3528

Received on Thu Jul 01 1993 - 17:57:19 CEST