Path: utzoo!attcan!utgpu!jarvis.csri.toronto.edu!mailrus!tut.cis.ohio-state.edu!rutgers!devon!ctdi!mikei
From: mikei@ctdi.UUCP (Mike Israel)
Newsgroups: comp.databases
Subject: Re: Simple SQL question
Keywords: SQL
Message-ID: <790@ctdi.UUCP>
Date: 7 Dec 89 14:45:47 GMT
References: <98813@ti-csl.csc.ti.com> <2510@bilpin.UUCP> <6518@tank.uchicago.edu> <2511@bilpin.UUCP>
Reply-To: mikei@ctdi.UUCP (Mike Israel)
Organization: Communications Test Design, Inc., West Chester, Pa.
Lines: 47

In article <2511@bilpin.UUCP> you answered the question::
>> 
>> >>   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 then stated that:
>
>    Some points to bear in mind with regard to this solution:
>
>    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;


I am not familiar with Oracle, but I can confirm that the query as written
above does not work in Ingres (at least not as entered).  I posted a follow
up msg with a similiar solution that would work for Ingres:

select * from mytable d
where 10>=all
        (select count(age)
         from mytable d2
         where d2.age >= d.age);

I have run this query against a table containing multiple duplicate birthdates.
I always get the OLDEST TEN employees, I never get back more than ten rows.
Perhaps if Oracle will allow you to enter a query as I have shown it then you 
will get the desired ten rows without any unexpected extras.  I would be
interested to know if this works out.

Regards,
-- 
Michael A. Israel               ||  uucp: mikei@ctdi.UUCP
                               	||        ...!uunet!cbmvax!ctdi1!ctdi
Communications Test Design Inc.	||
West Chester, PA                || Please direct all complaints to /dev/null