How to sort a resultset from a ‘group by’ statement

500 pts.
Tags:
Oracle 11g
Oracle 11g Database
Oracle Query
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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • philpl1jb
    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')
    51,305 pointsBadges:
    report
  • hussain22juzer
    thanks alot that worked....
    500 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following