CODEWITHSUNDEEP
sqloracle-database
suraha
suraha

Reputation: 387

order by clause not showing expected result

when i run the following query where i need to use trim function on date, the order of output is not proper

  select trim(man_date_created)as createddate,count(*) recordcount
  from man 
  where man_date_created>sysdate-15 
  group by trim(man_date_created) ORDER BY createddate;

this the out put i am getting from this query 

01-APR-16
02-APR-16
03-APR-16
04-APR-16
05-APR-16
06-APR-16
07-APR-16
08-APR-16
09-APR-16
10-APR-16
11-APR-16
27-MAR-16
28-MAR-16
29-MAR-16
30-MAR-16
31-MAR-16

where you can see that after 11 april its showing entries of march.
is there any solution for this so that i cant get the count of all status?

Upvotes: 0

Views: 119

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

You should convert your string in date 

SELECT TO_DATE('12-4-2016','YYYY-MM-DD');



select trim(DATE(date,'YYYY-MM-DD'))as createddate,count(*) recordcount
from man 
where man_date_created>sysdate-15 
group by trim(man_date_created) ORDER BY createddate;

in your case try this 

select DATE(mandate,'YYYY-MM-DD') createddate, count(*) recordcount, 
count(case when man_status = 'A' then 1 end) as a, 
count(case when man_status = 'S' then 1 end) as s, 
count(case when man_status = 'C' then 1 end) as c, 
count(case when man_status = 'R' then 1 end) as r 
from man 
where man_status IN ('A','S','C','R') and mandate>sysdate-15 
group bycreateddate ORDER BY createddate;

Upvotes: 2

marlan
marlan

Reputation: 1485

You have to convert the string to date in the ORDER BY clause:

select trim(date)as createddate,count(*) recordcount
from man 
where man_date_created>sysdate-15 
group by trim(man_date_created) ORDER BY TO_DATE(date, 'DD/Month/YYYY');

Upvotes: 1

Related Questions