How to sort a resultset from a ‘group by’ statement
I have written a query like this:
select to_char(order_date,'mon-yyyy') "months", sum(nob) "number of bags"
from p_in where order_date between '1-apr-11 and '31-mar-12'
group by to_char(order_date,'mon-yyyy')
The above query returns me a resultset which gives me number of bags sold in every
month, but it does not sort the month in their chronological order.I want to sort my resultset in ascending order of month s.,means jan-feb-march-apr-may ams so on...
Software/Hardware used:
oracle
Software/Hardware used:
oracle
ASKED:
March 9, 2013 4:20 AM
UPDATED:
March 11, 2013 12:36 PM
Approved Answer - Chosen by hussain22juzer (Question Asker)
Order by a column that will produce the desired results ..
select to_char(order_date,'mon-yyyy') "months",
sum(nob) "number of bags"
from p_in
where order_date between '1-apr-11' and '31-mar-12'
group by to_char(order_date,'mon-yyyy')
order by to_char(order_date,'mon-yyyy')
will put the list in month name alpha order
.. Apr 2011 folowed by Apr 2012 .. not the order you want
So you need the date in a format that can be sorted properly.
select to_char(order_date,'mon-yyyy') "months",
sum(nob) "number of bags",
to_char(order_date,'yyyymm') "YearMM"
from p_in
where order_date between '1-apr-11' and '31-mar-12'
group by to_char(order_date,'mon-yyyy'),
to_char(order_date,'yyyymm')
order by to_char(order_date,'yyyymm')
Add an ORDER BY clause after your GROUP BY clause. SQL never guarantees any sorted sequence if you don’t specify ORDER BY.
Related scenario:
You develop on a small, older development server that’s a fix-pack behind. You run your query 1000 times with all kinds of different WHERE clauses to test all reasonable requests. Your results come out perfectly every time.
You’re satisfied with testing, so you promote to the big, new production server. The big server is a 16-core system, and each core can run dual threads. The server has SMP enabled, and it runs with all of the latest fixes applied.
But the results always come out in a messed up sequence. Why?
In this case, SMP can assign different blocks of the table to different cores. Each core adds its results at slightly different times. But because they all come from different parts of the base table, they show in the final result in the order they were processed.
The query runs really fast on the production server compared to development, but it looks like it gives an incorrect result. But the result is exactly what was requested because no ORDER BY was specified. The order was allowed to be whatever happened.
The rule is “If you want an order, use ORDER BY.”
Tom
thanks alot that worked….