Skip to main content
K
keramatali
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

B
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.

B
bregent
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.

K
keramataliAuthor
Known Participant
July 30, 2009

Thank you so much! that fixed it!

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices