How to Sort Rows in SQL Shortcuts – The Easy Way

Sorting in SQL allows keeping the rows either Ascending or Descending. Two ways the sorting you can. One is with ORDER BY and the other one with DISTINCT.

1. Sort using ORDER BY

When you use the ORDER BY clause, the column name on which you are ordering should not be present in the SELECT statement.

Here is an example:

SELECT emp_no, last_name, first_name 
FROM emp
ORDER BY work_dept;

Here, the work_dept uses for ordering. So it is not given in SELECT. You had better limit the number of columns to participate in the sort. Else, sorting might take longer.

The purpose of Group By

Grouping with SQL is helpful when you want to return aggregate data. Consider the following query:

Here’s an example:

SELECT work_dept, SUM(salary) 
FROM emp
GROUP BY work_dept; 

The output will have work_dept and its aggregate sum of salaries for all the employees in the work_dept. The order of the work_dept is random since it does not use the ORDER BY clause.

2. Sort with DISTINCT

The DISTINCT keyword internally does sort, even if there are no duplicate rows. Keep this in mind the overhead for DISTINCT  is high. 

However, this is not a recommendation to never use it as there may be very valid reasons for doing so.

The purpose of HAVING

Additionally, the HAVING clause eliminates departments that are of no use.

Here’s an example:

SELECT work_dept, SUM(salary)   
FROM emp    
GROUP BY work_dept    
HAVING AVG(salary) < 5000;

Related posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.