date_format and order by date problem
793966Aug 26 2010 — edited Aug 27 2010Hi,
I have a problem with taking the resultset as sorted by date.
example1:
SELECT
T.foreign_network_id as "Network ID",
date_format(T.ARRIVAL_DATE,'%d-%m-%Y') as "Date",
sum(case when((networks_rejected = 0)
and (T.sor_reapply = 'N')
and (T.sor_reason in ('NP', 'NU', 'NT', 'HT')))
then T.CNT else 0 end) as "Before Steering",
sum(case when(sor_reason in ('NP', 'HT', 'T2', 'TM', 'NM', 'MM', 'IPMM', 'NPMM'))
then T.CNT else 0 end) as "After Steering"
FROM ST_INC T,
NETWORKS N,
ZONE_NW_MAP ZN,
ZONES Z,
PAYMENT_TYPE PT
WHERE T.FOREIGN_NETWORK_ID = N.NETWORK_ID
AND ZN.NETWORK_ID = N.NETWORK_ID
AND Z.ZONE_ID = ZN.ZONE_ID
AND PT.id = T.ptype
AND opcode = 2
AND sor_action is not null
and Z.ZONE_ID = 269
group by
date_format(T.ARRIVAL_DATE,'%d-%m-%Y'),
T.foreign_network_id
order by date_format(T.ARRIVAL_DATE,'%d-%m-%Y')
There is no problem with example 1 however it sorts the result set as if the date field is a string. I know this should be because I use date_format with order by. When I delete and only use T.ARRIVAL_DATE, I get "not a group by expression" error. And the reason why I use date_format is that sometimes I want to retrieve different format such as '%m-%Y' or only '%Y'. So I am required to use date_format, but then I lose the chance to sort by the actual date. I tried to select "T.ARRIVAL_DATE" as another extra colum and sort with respect to that. But this time I lost tha chance to select it again in the date_format function. Can anybody help me, I am messed up here?
Edited by: user13378594 on Aug 26, 2010 3:00 PM