Home » SQL & PL/SQL » SQL & PL/SQL » Problem in ordering and formatting month (merged)
Problem in ordering and formatting month (merged) [message #342063] Thu, 21 August 2008 04:12 Go to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi,

I create on view by using below query,
select * from (select month,100 seq from time
union
select 'ALL',1 from dual)order by seq.

It is working fine and it displaying
ALL
APR
AUG
DEC
FED
JAN.........
now my problem is i want to put this months in order and ALL is in samr position means data i want to look like
ALL
JAN
FED
MAR
APR
MAY....

How can we do this,any one help me.

Re: Problem in ordering month? [message #342064 is a reply to message #342063] Thu, 21 August 2008 04:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Look into using the CASE statement in your order by clause.
Re: Problem in ordering month? [message #342125 is a reply to message #342063] Thu, 21 August 2008 07:48 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
You could also add a second order by column which turns your months into dates with a TO_DATE and your ALL (for the purpose of the TO_DATE) into a NULL
Re: Problem in ordering month? [message #342130 is a reply to message #342125] Thu, 21 August 2008 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Will NULL comes first or last?

Regards
Michel
Re: Problem in ordering month? [message #342135 is a reply to message #342130] Thu, 21 August 2008 08:10 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
It is irrelevant as the only month which goes to NULL (ALL) would be ordered by the main order by which is a number of 1 for ALL and 100 for all others.


EDIT: Though I suppose if he just added a NULLS FIRST to the order by condition he could throw out the sequence all together.

[Updated on: Thu, 21 August 2008 08:15]

Report message to a moderator

Re: Problem in ordering month? [message #342141 is a reply to message #342135] Thu, 21 August 2008 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Though I suppose if he just added a NULLS FIRST to the order by condition

Yes, and it is relevant per OP question: he asked to get ALL first.

Regards
Michel
Re: Problem in ordering month? [message #342145 is a reply to message #342141] Thu, 21 August 2008 09:16 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Do you not even bother to read my replies?

It was irrelevant because the original response told him to add a SECOND order by column the all would have been at the top due to the fact that is was already ordered by the seq value of 1 vs 100.
Re: Problem in ordering month? [message #342166 is a reply to message #342145] Thu, 21 August 2008 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you bother to post optimal answer?
Why adding a second order field when you can do it in only one field?

By the way, don't you see you give the correct answer or do you not even bother to read my replies?

Regards
Michel
Problem in formating month column? [message #342475 is a reply to message #342063] Fri, 22 August 2008 08:02 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi,

In my database i have month Column it displaying months in disorder order.But i want to see the month in order from Jan to Dec(means Jan,Feb,Mar,Apr,May...like that).How can we put in order.What condition we want to use. Can any one help me?
Sad
Re: Problem in formating month column? [message #342476 is a reply to message #342475] Fri, 22 August 2008 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here are 2 ways:
1/ Use decode to convert them in the month number
2/ Use to_date then to_char+to_number to get the month number

Regards
Michel

[Updated on: Fri, 22 August 2008 08:05]

Report message to a moderator

Re: Problem in formating month column? [message #342477 is a reply to message #342476] Fri, 22 August 2008 08:07 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
If u dont mind can u send the query for any one of method please
Re: Problem in formating month column? [message #342479 is a reply to message #342477] Fri, 22 August 2008 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If YOU (don't use IM speak) don't mind try the ways I mentioned and come back with what you tried if you don't get a solution.

DECODE/CASE
TO_DATE
TO_CHAR
TO_NUMBER

Regards
Michel
Re: Problem in formating month column? [message #342480 is a reply to message #342479] Fri, 22 August 2008 08:16 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Question has already been asked by this poster
here
Previous Topic: Privileges error
Next Topic: Reading images raises errors ! HOw to handle !?
Goto Forum:
  


Current Time: Sun Dec 11 00:34:03 CST 2016

Total time taken to generate the page: 0.13316 seconds