Last 20 dates, but in forward order

[OsakaWebbie]

I have a table with a date field (among other things, of course), and currently I'm pulling in all the records that apply to certain criteria and charting it by date, but the number of dates is getting out of hand. So I want to limit the query to the last 20 dates, and then give the user the option to ask for more historical data if they want.

At face value, the most obvious way is "ORDER BY MyDate DESC LIMIT 20", but if possible I would like to get the results in ascending order, not descending. Is there an easy way to do that? Or do I need to pull all the data into an array (in my PHP code) and step through it backwards?

 

[TonyGroves]

You could use a subquery (available in MySQL 4.1 onwards):
[tt]
SELECT *
FROM
(
SELECT *
FROM tbl
ORDER BY mydate DESC
LIMIT 20
) sq
ORDER BY mydate
[/tt]

 

[OsakaWebbie]

What about in 3.23? (I'm using a hosting service, so I don't have control over upgrades - this isn't the first time I wish I had 4.1 for the subquery capabilities.)

 

[vacunita]

I might be missing the point here, but can't you just use [red]ASC[/red] for the ORDER by clause, instead of DESC.

ORDER BY MyDate [red]ASC[/red] LIMIT 20

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[TonyGroves]

Vacunita:
Just using ASC will return the earliest 20 dates, not the latest 20.

OsakaWebbie:
Looks like you would have to use either two queries with a temporary table, or PHP code.

 

[vacunita]

O.k so i was missing the point sorry.

I think then that it would be lot simpler to just stick the results in an array and them sort them in PHP. but that's just me



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[OsakaWebbie]

Okay, I'll just do it in code - thanks.

 

[guelphdad]

If you are using a hosting service using mysql 3.23 then you need to find another host. You wouldn't be using Windows 95 still right? mysql 3 is significantly out of date, at least two major revisions to the software.

there are numerous hosts that offer cheap packages that are using at least mysql 4.1

 

[OsakaWebbie]

there are numerous hosts that offer cheap packages that are using at least mysql 4.1

That's true if you are only looking for hosting in English. If you need the control panel in Japanese, a webmail interface that doesn't screw up Japanese email, and MySQL and PHP set up properly for multibyte, the selection goes way down. There are only three Japanese PHP-capable hosters I know of whose price is affordable (most Japanese hosters are outrageously expensive, targeting businesses): one seems flakey, and the other two I do use, for a total of three domains under my care - both of those hosters are still at MySQL 3.23. I don't see any other options for me.

And this database I'm developing is currently installed on four different domains, and that may expand - when it comes to domains I didn't personally set up, I have no control over what hoster they use. So until lots more hosters upgrade past 4.1, the code needs to be relatively version-independent.