RDBMS Sorting and Grouping

Sorting and Grouping in RDBMS

When working with databases, you often need to organize and analyze data. For example, you may want to display products from the lowest price to the highest price, list employees alphabetically, or find out how many students belong to each class.

SQL provides two powerful features for these tasks: sorting and grouping. Sorting arranges data in a specific order, while grouping combines similar records into categories.

These features make data easier to read, understand, and analyze.

What is Sorting?

Sorting means arranging records in ascending or descending order based on one or more columns.

SQL uses the ORDER BY clause to sort data. By default, SQL sorts data in ascending order.

For example, consider the following Students table.

The records are not arranged in any particular order.

Sorting in Ascending Order

To display students by their names in alphabetical order:

SELECT *
FROM Students
ORDER BY Name ASC;

ASC stands for ascending. If you do not specify ASC, SQL uses ascending order by default.

Sorting in Descending Order

To display students from the highest age to the lowest age:

SELECT *
FROM Students
ORDER BY Age DESC;

DESC stands for descending. It is commonly used when displaying highest scores, latest dates, or most expensive products first.

Sorting by Multiple Columns

Sometimes one column is not enough for sorting. For example, you may want to sort students by city and then by name.

SELECT *
FROM Students
ORDER BY City ASC, Name ASC;

SQL first sorts the data by City. If multiple students belong to the same city, it then sorts those students by Name.

What is Grouping?

Grouping means collecting rows that have the same value in a specific column.

SQL uses the GROUP BY clause for grouping. Grouping is often used together with aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN().

Instead of displaying every individual record, grouping summarizes the data.

Grouping Example

Suppose you have the following Employees table.

To count how many employees work in each department:

SELECT Department,
       COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department;

Instead of listing every employee, SQL displays one row for each department along with the number of employees.

Combining GROUP BY with ORDER BY

Grouping and sorting are often used together.

SELECT Department,
       COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
ORDER BY TotalEmployees DESC;

This query groups employees by department, counts employees in each department, and displays departments with the highest employee count first.

Best Practices

When using sorting and grouping, follow these guidelines:

• Use ORDER BY when you want data displayed in a specific order.
• Use GROUP BY when summarizing records with the same values.
• Combine GROUP BY with aggregate functions like COUNT(), SUM(), and AVG().
• Use ORDER BY after GROUP BY to sort summary results.
• Select only the columns you need to improve query readability and performance.