Path: utzoo!attcan!utgpu!jarvis.csri.toronto.edu!mailrus!wuarchive!zaphod.mps.ohio-state.edu!samsung!emory!cambridge.apple.com!bloom-beacon!eru!luth!sunic!mcsun!ukc!icdoc!bilpin!jim
From: jim@bilpin.UUCP (JimG)
Newsgroups: comp.databases
Subject: Re: Simple SQL question
Summary: Some clarification seems to be necessary
Keywords: SQL
Message-ID: <2511@bilpin.UUCP>
Date: 4 Dec 89 13:42:44 GMT
References: <98813@ti-csl.csc.ti.com> <2510@bilpin.UUCP> <6518@tank.uchicago.edu>
Organization: SRL, London, England
Lines: 52


    #{v_databases.10a}

    IN ARTICLE <6518@tank.uchicago.edu>, 
		monty@delphi.uchicago.edu (Monty Mullig) WRITES:
>   IN ARTICLE <2510@bilpin.UUCP> jim@bilpin.UUCP ( that's me ) WRITES:
> 
> >    IN ARTICLE <98813@ti-csl.csc.ti.com>, 
> >		hemphill@csc000.csc.ti.com (Charles Hemphill) WRITES:
> >>   Can the following type of question be answered with an SQL expression?
> >>   	List the 10 oldest employees.
> >
> >    It can be done in one SQL statement as follows :
> >    SELECT * FROM tablename tablealias
> >	WHERE 10 > (	SELECT COUNT(*) FROM tablename
> >				WHERE tablename.age > tablealias.age
> >		   )
> 
> 
> you mean, a statement like this:
> select * from master m1, master m2 
> 	where 10 > (select count(*) from m1 
> 		    where m1.bdate < m2.bdate);
> funny, but INGRES (5.0) on my machine choked on this query.  now, is
> INGRES at fault or is the SQL wrong ?

    Well, no, I didn't mean that, which is why I didn't write that. The
    query structured as I entered it worked fine in Oracle 5 - obviously I
    checked it before posting - the approach is sound. Please e-mail if you
    want to discuss the problems with your alternative - they aren't
    relevant to the original question, so I won't cover them here.

    Some points to bear in mind with regard to this solution:
    a) the 'age' would obviously not be held as a column in the table -
       'age' would be a function calculating the age from birth date and
       current date - preferably to an accuracy which would minimise the
       possibility of two or more people having the same age; because
    b) if two or more people do have the same age, even to the day (minute,
       picosecond, ...) the query may return more than 10 rows, if some of
       those people occur in the oldest 10; eg. if age is only calculated to
       the year, and the 8 oldest are 62, and the next four oldest are 60,
       you will get back 12 rows - in these circumstances the query can only
       return the set of rows within which the oldest 10 will be contained,
       as the data is not sufficiently accurate to do otherwise;
    c) the same approach should be feasible for cutting out a section of
       rows, by setting both top and bottom limits, with two sub-queries,
       although it's going to be reading the table an awful lot of times.
-- 
    Another Fine Product from <mcvax!ukc!icdoc!bilpin!jim> <jim@bilpin.uucp>
			   {JimG : Hatfield, England}
			       Programmers' Maxim
	    If it's not aesthetically pleasing, it's probably wrong