How to Sort Rows in SQL Shortcuts – The Easy Way

Soring in SQL, you can do it in two ways – ORDER By and DISTINCT in the SELECT statement.   

Sorting with ORDER BY

One prime point of the ORDER BY clause is that the column(s) mentioned in that clause is NOT present in the output.

Here is an example:

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

Here, the work_dept does not appear in the output, but sorting happens on that column. You should also limit the number of columns to participate in the sort. Sorting takes longer when you include more columns.

Sorting with DISTINCT

The DISTINCT keyword always causes a sort, even if there are no duplicate rows. 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.

Role of Group By

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

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

The output contains a single line for each work_dept. And the aggregate sum of salaries for all the employees in the work_dept. 

The order of the work_dept is random since no it does not use the ORDER BY clause.

Role of HAVING

Additionally, the HAVING clause can eliminate departments that are of no use to you:

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.