CODEWITHSUNDEEP
mysqlsql
user1592380
user1592380

Reputation: 36227

order by date DESC not working in mysql

I have the following MYSQL table 

CREATE TABLE `mytable` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `subject` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `date` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `timestamp` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `UQ_4242c0a8c07abd9adef59c123f76f3827bb47589` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

my query is:

 select * from `mytable` order by `date` DESC

This is not ordering it correctly. What am I doing wrong?

Upvotes: 0

Views: 1852

Answers (2)

Sergio Ramirez
Sergio Ramirez

Reputation: 340

You should create yourdate field as a date data value

CREATE TABLE `mytable` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`subject` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`date` date DEFAULT NULL,
`timestamp` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `UQ_4242c0a8c07abd9adef59c123f76f3827bb47589` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Upvotes: 1

Ende Neu
Ende Neu

Reputation: 15773

The problem is that being date a varchar it's sorted in lexicographical order, try to cast it to date:

 select * from `mytable` order by STR_TO_DATE(`date`, '%m/%d/%Y') DESC

Anyway this is a temporary solution, you should fix your schema and store it with the appropriate format.

Upvotes: 5

Related Questions