OraFAQ Forum: SQL & PL/SQL » Order By Issue....

Home » SQL & PL/SQL » SQL & PL/SQL » Order By Issue....

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Order By Issue.... [message #204588]

Tue, 21 November 2006 06:06

sundarsoft
Messages: 15
Registered: October 2006

Junior Member

Hi

I am having query like this

select to_char(create_date,'Mon,yyyy'),sum(test)
from test1
group by to_char(create_date,'Mon,yyyy')
order by to_char(create_date,'Mon,yyyy')

I am getting results correct..
but orderby is consdered as STRING not by DATE
but i have order records by date

Plz help mee
Sundar

Report message to a moderator

Re: Order By Issue.... [message #204597 is a reply to message #204588]

Tue, 21 November 2006 06:22

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

Migth be my english......
But I havent got a clue what you are trying to accomplish.

Show a sample of the output from your query and then do
and "cut & paste" of how you would like it to look.

Report message to a moderator

Re: Order By Issue.... [message #204608 is a reply to message #204597]

Tue, 21 November 2006 06:33

sundarsoft
Messages: 15
Registered: October 2006

Junior Member

select to_char(mo.request_date,'Mon,yyyy') month,sum(mod.ordered_quantity) ordered
from material_order mo,material_order_detail mod
where mo.material_order_id = mod.material_order_id
and mo.request_date >= to_date('Aug,2005','Mon,yyyy')
group by to_char(mo.request_date,'Mon,yyyy')
order by to_char(mo.request_date,'Mon,yyyy')

here o/p is
-----------

Apr,2006 1
Aug,2005 2
.
.
.
But o/p must be

Aug,2005 2
Apr,2006 1

Order by is done based on string
but not on date

Report message to a moderator

Re: Order By Issue.... [message #204609 is a reply to message #204608]

Tue, 21 November 2006 06:36

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

So don't order it by TO_CHAR(date_column), but 'date_column' itself.

Report message to a moderator

Re: Order By Issue.... [message #204611 is a reply to message #204609]

Tue, 21 November 2006 06:38

sundarsoft
Messages: 15
Registered: October 2006

Junior Member

Then it is saying that NOT A GROUP BY EXPRESSION

Report message to a moderator

Re: Order By Issue.... [message #204614 is a reply to message #204611]

Tue, 21 November 2006 06:44

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

That's because you changed the select to_char(mo.request_date,'Mon,yyyy')
to select date_column
And dont group by the date_column, you still go the to_char(mo.request_date,'Mon,yyyy') as group by expression

Report message to a moderator

Re: Order By Issue.... [message #204616 is a reply to message #204611]

Tue, 21 November 2006 06:50

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Right, not a group-by expression ... sorry. How about this?

order by TO_DATE(to_char(mo.request_date,'Mon,yyyy'), 'Mon,yyyy')

Report message to a moderator

Re: Order By Issue.... [message #204617 is a reply to message #204614]

Tue, 21 November 2006 06:50

sundarsoft
Messages: 15
Registered: October 2006

Junior Member

i did not get u?

I need month wise data,so i grouped them by to_char(...)

but order by is not supported..

Report message to a moderator

Re: Order By Issue.... [message #204625 is a reply to message #204588]

Tue, 21 November 2006 07:29

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

Your GROUP BY expression has to be against what you have in your select clause....

"but order by is not supported.."
Dont have a clue what you are trying to say.

Open SQLPLUS , execute your select statment, then paste the whole thing here, then we might see what you are doing wrong

Report message to a moderator

Re: Order By Issue.... [message #204631 is a reply to message #204625]

Tue, 21 November 2006 07:49

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

An example: group by TO_CHAR, order by TO_DATE(TO_CHAR); it *seems* that records are correctly ordered.

SELECT TO_CHAR(hiredate, 'mon, yyyy') mon_yyyy, SUM(sal) sal
FROM EMP
GROUP BY TO_CHAR(hiredate, 'mon, yyyy')
ORDER BY TO_DATE(TO_CHAR(hiredate, 'mon, yyyy'), 'mon, yyyy')

Report message to a moderator

Previous Topic:	Update problem (merged)
Next Topic:	De-Dupe using Select

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 02:02:11 CST 2024