mysql ORDER BY date_created DESC does not work properly

[keramatali]

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.

[bregent]

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.

[keramatali]

Thank you so much! that fixed it!

[bregent · Accepted solution]

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.