Distinct Vs Group By: Optimal Method for Sorting Table Rows

Sorting rows in SQL helps retrieve older or newer entries. Instead of using ORDER BY, you can use DISTINCT or GROUP BY. The notes below provide a better approach. Tuning the SQL query is useful for easing the load on the RDBMS server.

ON THIS PAGE

1. Group By Clause
2. DISTINCT Clause

Group By Clause

ORDER BY Clause

When you use the ORDER BY clause, the column name on which you are ordering (Ascending order is default) should not be used 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 is used for ordering. So, it is not given in the SELECT. You had better limit the number of columns participating in the sort. Otherwise, sorting takes longer.

Grouping is another way of sorting 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;

In the output, you’ll find, the work_dept and its aggregate sum of salaries of all the employees in the work_dept. The order of the work_dept is random since it does not use the ORDER BY clause.

DISTINCT Clause

The DISTINCT internally does sort, even if there are no duplicate rows. Remember, overhead is high for the DISTINCT clause. But this is not a recommendation to never use it. There will be valid reasons for doing so.

Having clause

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;

Conclusion

In summary, the Group By is more efficient than the Distinct clause for sorting rows.