Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!wuarchive!uunet!mcsun!ukc!icdoc!bilpin!jim From: jim@bilpin.UUCP (JimG) Newsgroups: comp.databases Subject: Re: Simple SQL question Summary: A single SQL statement does it ... Keywords: SQL Message-ID: <2510@bilpin.UUCP> Date: 30 Nov 89 18:01:13 GMT References: <98813@ti-csl.csc.ti.com> Organization: SRL, London, England Lines: 33 {v_databases.10} 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. > > Basically, I'd like the top N tuples from an ordered table. I don't > want to use embedded SQL. 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 ) Now isn't that neat? How it works is : for each row, select the number of rows where the age is greater than the current row; if there are less than 10 rows which satisfy this condition, then the current row must be amongst the 10 oldest, so print it. -- Another Fine Product from {JimG : Hatfield, England} Programmers' Maxim If it's not aesthetically pleasing, it's probably wrong Brought to you by Super Global Mega Corp .com