Home » SQL & PL/SQL » SQL & PL/SQL » sorting the month
sorting the month [message #195884] Mon, 02 October 2006 22:51 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
There is one column holding period data like

<MON>-<YY>

what should be the filter creteri so that month would be in asc or dec order like

jan-06
feb-06...


..
..
dec-06
jan-07
feb-08

any input would be apprecaited
regards
sanjit
Re: sorting the month [message #195890 is a reply to message #195884] Tue, 03 October 2006 00:34 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
What is the datatype of this column? DATE (which it should be) or VARCHAR2?
Re: sorting the month [message #195892 is a reply to message #195890] Tue, 03 October 2006 01:03 Go to previous messageGo to next message
sanjit
Messages: 65
Registered: November 2001
Member
IT IS VARCHAR

AND I HAVE USED SUBSTR(COLUMN,5,2), IT ONLY HELPS IN SORTING TEH YEAR, BUT NOT THE MONTHWISE
Re: sorting the month [message #195895 is a reply to message #195892] Tue, 03 October 2006 01:12 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Todd is 100% right: if it represent dates, it should be stored as dates.

Here's a small test script. The idea is that you convert the varchar2 column to a date and sort on that result.

CREATE TABLE mhe_foo(col1 VARCHAR2(6))
/

INSERT INTO mhe_foo VALUES ('dec-06');
INSERT INTO mhe_foo VALUES ('jan-06');
INSERT INTO mhe_foo VALUES ('feb-08');
INSERT INTO mhe_foo VALUES ('feb-06');
INSERT INTO mhe_foo VALUES ('jan-07');

SELECT col1
FROM   mhe_foo
ORDER  BY to_date(col1,'mon-yy')
/

DROP TABLE mhe_foo
/


MHE
Previous Topic: Mirror Table
Next Topic: INLINE QUERY
Goto Forum:
  


Current Time: Tue Dec 06 06:30:56 CST 2016

Total time taken to generate the page: 0.09791 seconds