Re: DBMS and lisp, etc.

From: Rob Warnock (rpw3_at_rpw3.org)
Date: 05/23/04 

Date: Sat, 22 May 2004 20:11:13 -0500

Will Hartung <willh@msoft.com> wrote:
+---------------
| Typically we need "the latest" value for several of these aliases. As a
| generic SQL query, getting "the latest" is, essentially, a horrible query:
|
| SELECT * FROM Table WHERE alias = 'Alais' and date = (Select max(date) from
| table where alias = 'Alias')
|
| But, we also tend to need "the previous value" as well (which would be the
| one before the latest). Also, in one part of are system, we'll want several
| hundred of these values. Which pretty much means:
|
| SELECT * FROM Table WHERE alias = 'Alias' ORDER BY date DESC
|
| You'll note that this in fact potentially reads ALL of the historical data,
| even though in fact we only want the second row, particularly if DATE is not
| indexed properly. Not a problem with 5 rows. A real problem with 5000. So,
| somewhere between 5 and 5000 Happiness converts to Blood Boiling Rage.
+---------------

PostgreSQL supports the LIMIT qualifier, doesn't DpANS SQL?

   SELECT * FROM Table WHERE alias = 'Alias' ORDER BY date DESC LIMIT 10

That would get you just the 10 latest values, no matter how many rows
in the table matched.

-Rob

-----
Rob Warnock <rpw3@rpw3.org>
627 26th Avenue <URL:http://rpw3.org/>
San Mateo, CA 94403 (650)572-2607