Hi.
On Mon 2002-07-29 at 14:24:54 -0400, [EMAIL PROTECTED] wrote:
>
> That's a really weird problem. Perhaps someone else could reproduce it if
> it's a bug in the most recent MySQL.
It's not a bug.
> I can think of an immediate way to solve it; use the type (datetime?),
> which is really a string 20020729. I don't see how this could be sorted
> incorrectly, but check your types.
>
> On Mon, 29 Jul 2002, julian haffegee wrote:
>
> > Date: Mon, 29 Jul 2002 17:59:00 +0100
> > From: julian haffegee <[EMAIL PROTECTED]>
> > To: MySQL General List <[EMAIL PROTECTED]>
> > Subject: order by date
> >
> > This must be a regularly appearing problem, which is why i'm surprised I
> > can't find a webpage on it..
> >
> > I have
> >
> > $result = mysql_query("SELECT title, url, description, author,
> > date_format(date,'%D %M %Y') AS date FROM documents_tbl, url_tbl WHERE
> > documents_tbl.title_id = url_tbl.url_id ORDER BY date DESC");
> >
> > Everything works fine, but the ORDER BY bit sorts all dates as 9th, 8th 7th
> > etc rather than 31st, 30th and so on.
Of course, you sort by "date" which is aliased to
"DATE_FORMAT(date,'%D %M %Y')", which is a usual text, which has not
reason to be sorted numerically, as you would expected it to.
The solution is simply to sort by the original date value, which is
only possible, if you change the alias from "date" to something else,
e.g. "pretty_date" (btw, it's generally considered bad style to
overload existing names).
SELECT title, url, description, author,
DATE_FORMAT(date,'%D %M %Y') AS pretty_date
FROM documents_tbl AS d, url_tbl AS u
WHERE d.title_id = u.url_id
ORDER BY date DESC
If you really want to sort by day first, and month next, you would
have to make a seperate sorting column (which you ignore in your
application, of course):
SELECT title, url, description, author,
DATE_FORMAT(date,'%D %M %Y') AS pretty_date,
DATE_FORMAT(date,'%d%m%Y') AS order_date
FROM documents_tbl AS d, url_tbl AS u
WHERE d.title_id = u.url_id
ORDER BY order_date DESC
Greetings,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
