-
Sorting, sort of...
Date: 11/13/06 (MySQL Communtiy) Keywords: no keywords
I have a question
So, I have the following table:
+----------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------+------+-----+---------+-------+ | nid | int(32) | NO | PRI | | | | cid | int(11) | YES | | | | | ntitle | char(255) | YES | | | | | ndate | char(40) | YES | | | | | month | char(5) | YES | | | | | day | char(5) | YES | | | | | year | char(5) | YES | | | | | nvisible | int(1) | YES | | | | +----------+-----------+------+-----+---------+-------+
And I'm trying to sort by year, by month, and then by day -- pretty much like any blog would. My query looks something like this...
select * from [tablename] order by year desc, month desc, day desc
...but that doesn't organize them properly. I thought maybe I was doing it backwards, so I tried this...
select * from [tablename] order by day desc, month desc, year desc
...thinking it would organize the days, then organize them by months, and lastly by year. Still, nothing. They're all still out of order.
What am I doing wrong? How do I group the three columns (year, month, day -- 20061113) together so they get sorted chronologically? (And, yes, I know I should use time hashes or UTC/NTC; up until now I've always found it easier to deal with the dates as a string of characters.)
Thanks!
EDIT:
!['[info]']('http://stat.livejournal.com/img/userinfo.gif')
khamon pointed out that one problem is that I'm used char and not int which is my core sorting problem.
| « Remote DB backup | || | Finding bad data » |