CODEWITHSUNDEEP
sqlpostgresqlsortingdatetime
user2853879
user2853879

Reputation: 31

Query to sort the time with date in descending order in postgres

I am trying to sort the order date in descending order but here the date is comming in decending order but the time is not comming in the said order.

My query :

SELECT get_sub_test_name(test_id, sub_test_id) as testname,
       quantity, status, receipt_no, cash_book,            
       to_char(m.requi_date,'dd/mm/yyyy hh:MIAM') AS orderdate,  
       r.service_type, r.volume, r.requi_id, r.requi_year
FROM recommended_test as r , requisition_main as m 
WHERE r.requi_id=m.requi_id AND
      m.reg_no='1401490' AND
      m.hospital_id=17004 
ORDER BY m.requi_date::date DESC,orderdate DESC,m.requi_id DESC LIMIT 1000;

The orderdate is sorted in this manner:

15/01/2016 10:45AM    
15/01/2016 10:45AM
15/01/2016 08:52PM
15/01/2016 08:52PM

whereas i want it as :

                  15/01/2016 08:52PM
                  15/01/2016 08:52PM
                  15/01/2016 10:45AM
                  15/01/2016 10:45AM

Please help me.

Upvotes: 2

Views: 6289

Answers (1)

mjec
mjec

Reputation: 1817

You are selecting orderdate as a char, so it is being ordered lexically, where 6 1 > 6 0 ( in ../2016 08:52.. and ../2016 10:45..).

I don't know the type of m.requi_date but I suspect it is a datetime type. So you should get the result you are after by just ordering by m.requi_date DESC instead of m.requi_date DESC,orderdate DESC.

Upvotes: 2

Related Questions