Path: utzoo!attcan!utgpu!jarvis.csri.toronto.edu!cs.utexas.edu!uunet!odi!dlw
From: dlw@odi.com (Dan Weinreb)
Newsgroups: comp.databases
Subject: Re: Simple SQL question
Message-ID: <1989Dec4.211025.25593@odi.com>
Date: 4 Dec 89 21:10:25 GMT
References: <98813@ti-csl.csc.ti.com> <2510@bilpin.UUCP>
Reply-To: dlw@odi.com
Organization: Object Design, Inc.
Lines: 21
In-Reply-To: jim@bilpin.UUCP's message of 30 Nov 89 18:01:13 GMT

In article <2510@bilpin.UUCP> jim@bilpin.UUCP (JimG) writes:

   >   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?

Yes, it's neat.  I'd be interested in knowing how fast it is, compared
to the using embedded SQL with cursors (asking for them all, and just
using a cursor to get the first ten), on some real-world commercial
RDBMS's.  It seems to me that an extremely clever query optimizer
could do a great job here, by realizing that all it needs to do is
sort and grab the top ten, but I don't know whether or not any of the
commercial products do this kind of optimization well.


Brought to you by Super Global Mega Corp .com