Skip to main content
Known Participant
July 30, 2009
Answered

mysql ORDER BY date_created DESC does not work properly

  • July 30, 2009
  • 2 replies
  • 815 views

I have created a table to display data in my website. the data get displayed as intended except for anything I do before 10 AM stays on top. Please chek my website to see what I am talking about

www.dealfindit.com.

here is the code for relavent mysql table

SELECT title, merchant, image, price, price_price, dollar_sign, click_here, listprice_text, dealexpire_text, added_text,

to_shop, price_note, description, link_product, shipping_info, tax_info, other_info, deal_expire, list_price, category_items,

buying_instruction, DATE_FORMAT(date_created, '%m/%d/%Y %l:%i %p') AS 'date_created',DATE_FORMAT(date_created,'%W

%M %d %Y') AS 'date_only'
FROM mylist
WHERE date_created > SUBDATE(NOW(), 10)
ORDER BY date_created DESC

thnank you.

This topic has been closed for replies.
Correct answer bregent

OK, actually what I think is happening is you are sorting by the formatted date string, so it is sorting as a string. So 9:47 does preceed 11:45 in a descending string order. So you do need to change the alias name of the formatted date for your select column, but use the column name date_created in your order by clause.

This is one reason that aliases should not be named the same as columns.

2 replies

bregentCorrect answer
Participating Frequently
July 30, 2009

OK, actually what I think is happening is you are sorting by the formatted date string, so it is sorting as a string. So 9:47 does preceed 11:45 in a descending string order. So you do need to change the alias name of the formatted date for your select column, but use the column name date_created in your order by clause.

This is one reason that aliases should not be named the same as columns.

Participating Frequently
July 30, 2009

I don't know for sure, but one thing I see is that you are using an alias which has the same name as the column:

DATE_FORMAT(date_created, '%m/%d/%Y %l:%i %p') AS 'date_created',

Then the order by clause is using that same column/alias. Maybe this is confusing MySQL?  If MySQL supports alias's based on functions in the order by clause, you might want to change that alias name to something else, like date_created_alias and use that in the order by.

Otherwise, try using the complete function in the order by clause:

ORDER BY DATE_FORMAT(date_created, '%m/%d/%Y %l:%i %p')  DESC

But in any case, it sure seems like it should be sorting correctly the way you are currently handling it.

Known Participant
July 30, 2009

Thank you so much! that fixed it!