Home » SQL & PL/SQL » SQL & PL/SQL » order by month name (10g 10.1.0.2.0 - xp)
order by month name [message #397596] Sun, 12 April 2009 23:54 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Can I get the 'm' filed in correct ASCENDING order?

mean

JAN
FEB
MAR
etc


  1  select member_id,m,y,balance from net where member_id=2005100
  2* order by y, m
SQL> /

MEMBER_ M            Y    BALANCE
------- --- ---------- ----------
2005100 AUG       2007          0
2005100 DEC       2007       2540
2005100 JUL       2007       2065
2005100 JUN       2007       2065
2005100 NOV       2007       1070
2005100 OCT       2007        535
2005100 SEP       2007        425
2005100 APR       2008       1440
2005100 AUG       2008       3170
2005100 FEB       2008        585
2005100 JAN       2008          0
2005100 JUL       2008       3105
2005100 JUN       2008       3105
2005100 MAR       2008       1000
2005100 MAY       2008       2800
2005100 NOV       2008        485
2005100 OCT       2008          0
2005100 SEP       2008       4580

18 rows selected.


[Updated on: Mon, 13 April 2009 00:03]

Report message to a moderator

Re: order by month name [message #397599 is a reply to message #397596] Mon, 13 April 2009 00:07 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Try order by y, to_date(m,'MON')

Otherwise it treats it as a character and it is ordering as alphabetic.

By
Vamsi
Re: order by month name [message #397600 is a reply to message #397596] Mon, 13 April 2009 00:11 Go to previous message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Thank you for help..it works fine. the result is below

  1   select member_id,m,y,balance from net where member_id=2005100
  2*  order by y,to_date(m,'MON')
SQL> /

MEMBER_ M            Y    BALANCE
------- --- ---------- ----------
2005100 JUN       2007       2065
2005100 JUL       2007       2065
2005100 AUG       2007          0
2005100 SEP       2007        425
2005100 OCT       2007        535
2005100 NOV       2007       1070
2005100 DEC       2007       2540
2005100 JAN       2008          0
2005100 FEB       2008        585
2005100 MAR       2008       1000
2005100 APR       2008       1440
2005100 MAY       2008       2800
2005100 JUN       2008       3105
2005100 JUL       2008       3105
2005100 AUG       2008       3170
2005100 SEP       2008       4580
2005100 OCT       2008          0
2005100 NOV       2008        485

18 rows selected.

SQL> 
Previous Topic: total marks (merged)
Next Topic: table too fragmented to build bitmap index (67182634,104,104)
Goto Forum:
  


Current Time: Fri Dec 09 06:18:28 CST 2016

Total time taken to generate the page: 0.10983 seconds