How can i sort / group according to the date sql

Question

Whats the best way of grouping or sorting the following query, Also any way to improve the query would be appreciated.

SELECT TO_CHAR(B.EVENTDATE,'Month YYYY') Month_Year,
       LP_TOOLS.P_GETCODEDESCR_F('SomeOrgId', 'SomeOrgId','MSTS', TRIM(B.OLDSTATUS),'en') OLDSTATUS ,
       LP_TOOLS.P_GETCODEDESCR_F('SomeOrgId', 'SomeOrgId','MSTS', TRIM(B.NEWSTATUS),'en')  NEWSTATUS, 
       COUNT(*) STATUSCOUNT 
       FROM PTCIS_PRODUCTMEMBER A, PTLOY_STATUSCHANGE B 
        WHERE A.ORGID = 'SomeOrgId'
            AND A.PRODUCTID ='SomeOrgId'
            AND A.STATUS  NOT IN (SELECT STATUS 
                                  FROM PTGEN_STATUSVAL 
                                  WHERE INCLUDEGENREPORTS != 'Y' 
                                  AND ORGID=A.ORGID AND PRODUCTID=A.PRODUCTID)
            AND A.MEMBERTYPE IN ('INDV','COPM')
            AND A.ORGID=B.ORGID
            AND A.PRODUCTID=B.PRODUCTID
            AND A.MPACC = B.MPACC
            GROUP BY TO_CHAR(B.EVENTDATE,'Month YYYY'),B.OLDSTATUS,B.NEWSTATUS
            ORDER BY Month_Year DESC,OLDSTATUS,NEWSTATUS

This will give me wrong values example 2014 Jan, 2014 Feb, 2015 Feb , 2014 April etc

You should explain at least *why* those values are wrong and which values would be right. — GolezTrol, Aug 03 '15 at 08:54
I think he ment it's wrong because 2015 Feb should be after 2014 April. Anyhow we need more information to answer. What is Month_Year? What type is it? — John, Aug 03 '15 at 08:57
you are converting a date to a char and then sort by this char. But you need to order by the Date variable to get correct order. Try order by b.Eventdate desc, oldstatus... As char, Jan,Feb,Mar,Apr would order Mar, Jan, Feb, Apr descending — kl78, Aug 03 '15 at 08:57
@kl78 I thought of that too but it doesn't make seance as if Month_Year is string\text it should be ordered lexicographically and that wasn't the result he presented. — John, Aug 03 '15 at 08:59
Yes john, was also wondering about the 2015, but guessed this is a typo, because otherwise it would make no sense to me — kl78, Aug 03 '15 at 09:01
@kl78 Well if it's 2014 then there isn't really a question here because then it will be ordered :) — John, Aug 03 '15 at 09:02
Hi, Month_Year is a char atm but should be sorted by date. Also if i change the order by with B.EVENTDATE then it says not a valid group by. And i cannot group with B.Eventdate because it has days and times i just need Month and Year . Not sure if that explains? — Tinus Jackson, Aug 03 '15 at 09:06
It's hard to answer because there isn't much information but it seems like if you will convert your to_char back to date it will help you with the sorting: `to_date(to_char(B.EVENTDATE,'MON-YYYY'),'MON-YYYY')` — John, Aug 03 '15 at 09:15
you can also check this : http://stackoverflow.com/questions/17312967/need-date-in-the-format-yyyy-mm-in-mssql — kl78, Aug 03 '15 at 09:33

score 0 · Accepted Answer · answered Aug 03 '15 at 09:58

It's simple - in your order by clause, you need to convert the month_year column back into a date. Here's something that will hopefully explain it to you:

with sample_data as (select sysdate dt, 1 val from dual union all
                     select sysdate - 31.53 dt, 10 val from dual union all
                     select sysdate + 366 dt, 100 val from dual)
select to_char(dt, 'fmMonth yyyy') my,
       sum(val)
from   sample_data
group by to_char(dt, 'fmMonth yyyy')
order by to_date(to_char(dt, 'fmMonth yyyy'), 'fmMonth yyyy');

MY                                 SUM(VAL)
-------------------------------- ----------
July 2015                                10
August 2015                               1
August 2016                             100

or, since the order by clause can accept column aliases, simply:

with sample_data as (select sysdate dt, 1 val from dual union all
                     select sysdate - 31.53 dt, 10 val from dual union all
                     select sysdate + 366 dt, 100 val from dual)
select to_char(dt, 'fmMonth yyyy') my,
       sum(val)
from   sample_data
group by to_char(dt, 'fmMonth yyyy')
order by to_date(my, 'fmMonth yyyy');

MY                                 SUM(VAL)
-------------------------------- ----------
July 2015                                10
August 2015                               1
August 2016                             100

N.B. I used fm in my format model, since without it, you end up with extra spaces between the month and the year. You may not need to use it if you want all the years to line up in the results.

How can i sort / group according to the date sql

1 Answers1