Difference between group by and order by in sql server

This document provides difference between Group by and Order by in SQL Server

1. Difference between Group By and Order By in SQL Server
S.NoGroup By Order By
1 Meaning:
Group By performs grouping
operation i.e., it provides a way to
sub-total SQL Query results,or
perform some other aggregate
functions (GROUPING SETS,
CUBE, ROLLUP, WITH CUBE,
or WITH ROLLUP) on them.
Meaning:
Order By performs sorting operation
i.e., it provides a way to sort SQL
Query results like ascending or
descending . It does not affect what
shows up in the result set,only what
order it is displayed.
2 Used for:
Controlling the presentation of the
rows for the results of the
SELECT statement.
Used for:
Controlling the presentation of the
columns for the results of the
SELECT statement.
3 Change in order of columns:
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.
Change in order of columns:
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.
4 Allowed in Create View
Statement or not:
GROUP BY clause can be used in
the CREATE VIEW statement to
sort data.
Allowed in Create View Statement
or not:
ORDER BY clause is not allowed in
the CREATE VIEW statement.
5 Execution Sequence:
The GROUP BY clause is always
placed before the ORDER BY
clause in the SELECT statement.
Execution Sequence:
The ORDER BY clause is always
placed after the GROUP BY clause in
the SELECT statement.
6 Impact on performance:
As we know that Group By clubs
all the similar rows and display
only the distinct data. So here
clubbing and displaying distinct
data is an overhead.
Impact on performance:
As we now that Order By sorts the
data either in ascending order or in
descending order as specified in the
query. So here Sorting the data is an
overhead.
Example-1 for Group By:

2. select * from employee group by emp_name.
this gives the output data in the group of the emp_name.
Example-2 for Group By:
SELECT department, sum(salary)
FROM tblEmployee
GROUP BY department;
will give us salary totals by department, whereas the sum statement by itself would just give us the
grand total of all salaries in tblEmployee.
Example-3 for Group By:
Group By helps us to display any aggregate of any column based on a field what has repeated
names.
use AdventureWorksDW2008R2
go
select Title,SUM(BaseRate) as Rate from dbo.DimEmployee group by Title
Title Rate
1The Accountant 52.88
2Accounts Manager 34.74
3Accounts Payable Specialist 38
4Accounts Receivable Specialist57
5Application Specialist 109.62
6Assistant to the Chief Financial Officer13.46
7Benefits Specialist 16.59
8Buyer 164.42
9Chief Executive Officer 125.5
10Chief Financial Officer 120.19
11Control Specialist 33.65
12Database Administrator 76.92
Example-1 for Order By:
select * from employee order by emp_name desc,emp_no asc;
this query gives the output in the ascending order of the emp_no and descending order of the
emp_name.
Example-2 for Order By:

3. SELECT *
FROM tblEmployee
ORDER BY lastname;
will give us all tblEmployee data, in order of last name. If we want the results in descending
(reversed) order, simply add DESC to the end of the clause: ORDER BY lastname DESC;
Example-3 for Order By:
Order By clause helps us to display any table based on the values present on that particular column.
use AdventureWorksDW2008R2
go
select FirstName,Title from dbo.DimEmployee order by FirstName
First NameTitle
1A. Scott The Master Scheduler
2Alan Scheduling Assistant
3Alejandro Production Technician - WC40
4Alex Production Technician - WC45
5Alice Production Technician - WC50
6Amy European Sales Manager
7Andreas Quality Assurance Technician
8Andrew Production Supervisor -WC10
9Andrew Production Technician - WC45
10Andy Production Technician - WC30
11Angela Production Technician - WC50
12Anibal Production Technician - WC20
Summary:
1.All non-aggregate columns selected must be listed in the GROUP BY clause.
2.Integers cannot be used in the GROUP BY to represent columns after the SELECT keyword,
similar to using the ORDER BY clause.
3.The GROUP BY clause is generally not necessary unless using aggregate functions.
4. If we 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 we want our groups
sorted, always use an explicity ORDER BY after the GROUP BY. – Dave Costa
5.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.

And, further updates on difference between questions and answers, please visit my blog @
http://onlydifferencefaqs.blogspot.in/