On Mon, Feb 26, 2001 at 06:19:46PM +0100, Joseph Bueno wrote:
> Hi,
>
> I currently use a SELECT that looks like:
> SELECT field
> FROM some_table
> WHERE some_condition
> ORDER BY some_date DESC
> LIMIT 2000
>
> in order to get the most recent records from some_table.
>
> Since it is not possible (yet) to optimise an ORDER BY ... DESC
> by using an index; is it possible to rewrite SELECT statement:
>
> SELECT field
> FROM some_table
> WHERE some_condition
> ORDER BY some_date
> LIMIT ?????
>
> so that results will be ordered in ascending order and return the
> 2000 *last* records ?
Well, you could store an additional field which is like the opposite
of the date. Since dates normally count forward from some time in the
past, you just need something that counts backward from some time in
the future.
You might consider using a unixtime value. MySQL can convert from/to
unixtime easily (check the manual), and there is currently a known
upper bound on the date that a unixtime can represent.
So you could store a field which you compute by taking the current
unixtime (983213418) and subtracting it from the upper-bound.
Think of it as field = max_unixtime - current_unixtime
Then the numbers will be SMALLER as you go into the future, so you can
sort them easily.
But there are many cases in which this isn't a good idea. So it may
work for you, or it may not. :-)
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 328-7878 Fax: (408) 530-5454
Cell: (408) 439-9951
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
