Search

Thursday, June 16, 2011

Difference Between GROUP BY and ORDER BY

Group By : It is a way to sub-total your results,or perform some other aggregate functions on them.  
Order By : It is a simply a way to sort your results. It does not affect what shows up in your result set,only what order it is displayed. 


ORDER BY alters the order in which items are returned.
GROUP BY will aggregate records by the specified columns which allows you to perform aggregation functions on non-grouped columns (such as SUM, COUNT, AVG, etc).

The difference is exactly what the name implies: a group by performs a grouping operation, and an order by sorts.
If you do "SELECT * FROM Customers ORDER BY Name" then you get the result list sorted by the customers name.
If you do "SELECT IsActive, COUNT(*) FROM Customers GROUP BY IsActive" you get a count of active and inactive customers. The group by aggregated the results based on the field you specified.


Moreover: if you GROUP, the results are not necessarily sorted; although in many cases they may come out in an intuitive order, that's not guaranteed by the GROUP clause. If you want your groups sorted, always use an explicity ORDER BY after the GROUP BY. – Dave Costa


Group by and order by are two different clause in SQL. One group the result set  and other (ORDER BY) order the result set.


Processing of "group by" or "order by" clause often requires creation of Temporary tables to process the results of the query. Which depending of the result set can be very expensive.


Order of columns make no difference in the GROUP BY Clause. For Example 


GROUP BY A, B, C
and
GROUP BY C,A,B 
Both returns the same results.


Order of Columns makes difference in ORDER BY Clause.For Example


ORDER BY A, B, C
and
ORDER BY C,A,B 
Both returns the same number of Records. But Row order will be different.

7 comments:

  1. So when you are using a group by some of other aggregate function should be used.

    ReplyDelete
  2. Did you credit the authors from stack overflow for taking their answers? http://stackoverflow.com/questions/1277460/what-is-the-difference-between-group-by-and-order-by-in-sql

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. I have seen that you have some of the category of apple,google,microsoft. that all are very interesting. We have similar updates on the same topics. If you are technology lover then must visit once. You'll grab updated knowledge of technology in one click Check Here : Technogupshup - Trending Technology Updates

    ReplyDelete