Path: utzoo!attcan!uunet!cs.utexas.edu!swrinde!gem.mps.ohio-state.edu!brutus.cs.uiuc.edu!apple!oracle!news From: ltung@oracle.com (Lei Tung) Newsgroups: comp.databases Subject: Re: Simple SQL question Message-ID: Date: 20 Nov 89 19:20:37 GMT References: <98813@ti-csl.csc.ti.com> Sender: news@oracle.com Organization: Oracle Corporation Lines: 37 In-reply-to: hemphill@csc000.csc.ti.com's message of 18 Nov 89 05:37:11 GMT In article <7@csc000.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. The following works in Oracle's SQL (I really have no idea if it is ANSI compliant). It is tricky since a subquery is being compared to a constant. I make no claims as to this statement's performance. select name, birthdate from employee e1 where 10 > (select count(*) from employee e2 where e2.birthdate < e1.birthdate); Note that results may be screwed if employees share birthdays. Hope this helps, Lei Tung Oracle Corp. -- *------------------------------------------------------------------------* | email: ltung@oracle.com | | or: {hplabs,apple,pacbell,mit-eddie,uunet}!oracle!ltung | | | | It'll all work out, | | you know I'm a little freaked out . . . | | - D. B. | *------------------------------------------------------------------------*