-4

Heres my select query,

select rac_username, rac_profilepicture, tafd_postid, tafd_postcontent, tafd_postimage_source, DATE_FORMAT(tafd_postadded,'%M %d, %Y') tafd_postadded,tafd_imotion, (select count(rpg_actormakeget) from r_post_getyou where rpg_postrelate = tafd_postid and rpg_isremoved = 0) as tafd_igetyoucount, (select count(rpg_postrelate) from r_post_getyou where rpg_postrelate = tafd_postid and rpg_actormakeget = (select rac_accountid from r_account_credentials where rac_username = 'zheuswalker' )) as isliked, (select count(rfc_commentid) from r_feeds_comments where rfc_feedparent = tafd_postid) rfc_commentcount from t_account_feeds inner join r_account_credentials on r_account_credentials.rac_accountid = t_account_feeds.tafd_postcreator  
ORDER BY STR_TO_DATE (tafd_postadded,'%M %d, %Y') ASC

I already, tried ordering the date by desc, but still i dont get the expected result. I want that the date will be sorted in ascending order (Latest to Oldest).

I tried also ordering it by this ( ORDER BY tafd_postadded asc ) and see picture, notice that March 2019 is in between June 2018, November 2018

Ken White
  • 123,280
  • 14
  • 225
  • 444
zheus walker
  • 1
  • 3
  • 1
    That is one off the reasons why you should use native DATE datatype instead off CHAR or VARCHAR to store dates and convert it with `STR_TO_DATE(..)` – Raymond Nijland Mar 01 '19 at 14:28
  • 1
    *"...in ascending order (latest to oldest)..."* Ascending date order is *earliest* (oldest) to *latest* (and the dates in your first screenshot are in that order). "latest to oldest" would be *descending*. – T.J. Crowder Mar 01 '19 at 14:30
  • 2
    The order seems right .. you have a date asc so what your goal ??? – ScaisEdge Mar 01 '19 at 14:30
  • @RaymondNijland - It's June **2018** and March **2019** in the first screenshot. (We can ignore the second, which is sorted lexicographically.) – T.J. Crowder Mar 01 '19 at 14:32
  • @RaymondNijland . i see march 01,2019 after january 10, 2019 .. could be we are lloking to different image – ScaisEdge Mar 01 '19 at 14:32
  • T.J.Crowder and scaisEdge never mind i didn't notice the records contains multiple years also. – Raymond Nijland Mar 01 '19 at 14:34
  • So just to clear this question, the first image was ordered by str_to_date , order asc is not working, nothing happes when you add asc or desc at the end. While on the second picture, it was ordered natively, btw, the tafd_postadded was DateTime – zheus walker Mar 01 '19 at 14:36

2 Answers2

0

STR_TO_DATE compares strings, so it is normal that Mars is before November as letter M is before letter N.

You should do : 

ORDER BY tafd_postadded DESC

Desc for Latest to Oldest
Asc for Oldest to Latest

Philoupe
  • 101
  • 8
0

Im using datetime on my tafd_postadded column, so when sorting datetime we should use UNIX_TIMESTAMP();

select * from table order by UNIX_TIMESTAMP(datetime column) desc
Teamothy
  • 2,000
  • 3
  • 16
  • 26
zheus walker
  • 1
  • 3