Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

mysql ORDER BY date_created DESC does not work properly

New Here ,
Jul 30, 2009 Jul 30, 2009

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.

TOPICS
Server side applications
821
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

LEGEND , Jul 30, 2009 Jul 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.

Translate
LEGEND ,
Jul 30, 2009 Jul 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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 30, 2009 Jul 30, 2009
LATEST

Thank you so much! that fixed it!

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 30, 2009 Jul 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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines