Home » SQL & PL/SQL » SQL & PL/SQL » Order By Issue....
Order By Issue.... [message #204588] Tue, 21 November 2006 06:06 Go to next message
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
Re: Order By Issue.... [message #204597 is a reply to message #204588] Tue, 21 November 2006 06:22 Go to previous messageGo to next message
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.
Re: Order By Issue.... [message #204608 is a reply to message #204597] Tue, 21 November 2006 06:33 Go to previous messageGo to next message
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
Re: Order By Issue.... [message #204609 is a reply to message #204608] Tue, 21 November 2006 06:36 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So don't order it by TO_CHAR(date_column), but 'date_column' itself.
Re: Order By Issue.... [message #204611 is a reply to message #204609] Tue, 21 November 2006 06:38 Go to previous messageGo to next message
sundarsoft
Messages: 15
Registered: October 2006
Junior Member
Then it is saying that NOT A GROUP BY EXPRESSION
Re: Order By Issue.... [message #204614 is a reply to message #204611] Tue, 21 November 2006 06:44 Go to previous messageGo to next message
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

Re: Order By Issue.... [message #204616 is a reply to message #204611] Tue, 21 November 2006 06:50 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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')
Re: Order By Issue.... [message #204617 is a reply to message #204614] Tue, 21 November 2006 06:50 Go to previous messageGo to next message
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..
Re: Order By Issue.... [message #204625 is a reply to message #204588] Tue, 21 November 2006 07:29 Go to previous messageGo to next message
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
Re: Order By Issue.... [message #204631 is a reply to message #204625] Tue, 21 November 2006 07:49 Go to previous message
Littlefoot
Messages: 20901
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')
Previous Topic: Update problem (merged)
Next Topic: De-Dupe using Select
Goto Forum:
  


Current Time: Sun Dec 11 07:56:02 CST 2016

Total time taken to generate the page: 0.12039 seconds