9.01.2012

Group By vs Order By in SQL Server

Difference between Group By and Order By in SQL Server

S.No Group 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:

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
1 The Accountant 52.88
2 Accounts Manager 34.74
3 Accounts Payable Specialist 38
4 Accounts Receivable Specialist 57
5 Application Specialist 109.62
6 Assistant to the Chief Financial Officer 13.46
7 Benefits Specialist 16.59
8 Buyer 164.42
9 Chief Executive Officer 125.5
10 Chief Financial Officer 120.19
11 Control Specialist 33.65
12 Database 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:

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 Name Title
1 A. Scott The Master Scheduler
2 Alan Scheduling Assistant
3 Alejandro Production Technician - WC40
4 Alex Production Technician - WC45
5 Alice Production Technician - WC50
6 Amy European Sales Manager
7 Andreas Quality Assurance Technician
8 Andrew Production Supervisor -WC10
9 Andrew Production Technician - WC45
10 Andy Production Technician - WC30
11 Angela Production Technician - WC50
12 Anibal 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.

No comments:

Post a Comment