CODEWITHSUNDEEP
sqloracle-databasedateselectsql-order-by
Mani
Mani

Reputation: 39

SQL datepart mmm yy not sorted in chronological order

I am not able to sort the mmm yy summary using date part coz I wanted month year format

Got the output as below

Col1   Col2
AA     Apr 19
BB     Feb 20
CC     Dec 19 
DD     May 19
EE     Jan 20

How to sort this month year wise

Expected result

Col1   Col2
AA     Apr 19 
DD     May 19
CC     Dec 19
EE     Jan 20
BB     Feb 20

Upvotes: 0

Views: 470

Answers (3)

Alex Poole
Alex Poole

Reputation: 191285

It's a query that converts date to mmm yy format

As you are working with months, you are presumably aggregating daily data by month, and are currently doing something like:

select count(*) as col1,
  to_char(your_column, 'Mon RR', 'NLS_DATE_LANGUAGE=ENGLISH') as col2
from your_table
group by to_char(your_column, 'Mon RR', 'NLS_DATE_LANGUAGE=ENGLISH')
order by col2;

which orders the data by the string representation of the date. (That isn't exactly what you show in your sample, but not sure if you just created that oddly...)

If you aggregate using the actual month instead, by truncating the date values (giving the first day of that month), then you can group by and order by that truncated date; and just convert to a string at the last moment for display only:

select count(*) as col1,
  to_char(trunc(your_column, 'MM'), 'Mon RR', 'NLS_DATE_LANGUAGE=ENGLISH') as col2
from your_table
group by trunc(your_column, 'MM')
order by trunc(your_column, 'MM');

db<>fiddle

Like @GMB, I've specified the language to use for the month abbreviations.

Upvotes: 1

GMB
GMB

Reputation: 222482

In Oracle, you can use to_date() for this:

select col1, col2
fro mytable 
order by to_date(col2, 'mon yy')

Note that Oracle interprets the month name according to NLS parameters, such as NLS_DATE_LANGUAGE. So for this to work, your database's language must be American.

If that's not the case, you can try and force the parameter as a third argument to to_date():

to_date(col2, 'mon yy', 'NLS_DATE_LANGUAGE=American')

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269933

One method is:

order by year, 
         (case month when 'Jan' then 1 when 'Feb' then 2 . . . end)

There may be simpler ways to express this, depending on your database.

Upvotes: 1

Related Questions