📊 The ORDER BY clause is used to sort the result of an SQL query in either ascending or descending order. By default, SQL returns records in an unspecified order unless an ORDER BY clause is used. Sorting data makes query results easier to read, analyze, and present.

📖 What is ORDER BY?

The ORDER BY clause arranges rows based on the values of one or more columns. You can sort numeric values, text, dates, and even calculated expressions. It is most commonly used with the SELECT statement, although it can also be used in queries involving joins, subqueries, and views.

📝 Basic Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC | DESC];

📊 Sample Table

Assume the following Students table:

⬆️ Sorting in Ascending Order

Use ASC to sort data in ascending order. If neither ASC nor DESC is specified, SQL sorts the data in ascending order by default.

SELECT *
FROM Students
ORDER BY Name ASC;

SELECT *
FROM Students
ORDER BY Name;

⬇️ Sorting in Descending Order

Use DESC to sort data from highest to lowest, or from Z to A for text values.

SELECT Name, Marks
FROM Students
ORDER BY Marks DESC;

📑 Sorting by Multiple Columns

You can sort results using more than one column. SQL first sorts by the first column and then uses the next column to resolve ties.

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

🔍 Combining WHERE and ORDER BY

The WHERE clause filters rows before the ORDER BY clause sorts the resulting records.

SELECT Name,
       Department,
       Marks
FROM Students
WHERE Department = 'Computer Science'
ORDER BY Marks DESC;

🧮 Sorting by Calculated Values

You can sort query results based on calculated expressions or aliases created in the SELECT statement.

SELECT Name,
       Marks,
       Marks + 5 AS BonusMarks
FROM Students
ORDER BY BonusMarks DESC;

🔢 Sorting by Column Position

SQL also allows sorting by the position of a column in the SELECT list. Although supported by many database systems, using column names is generally more readable and maintainable.

SELECT StudentID,
       Name,
       Marks
FROM Students
ORDER BY 3 DESC;

📊 ORDER BY with NULL Values

When sorting columns that contain NULL values, different database systems may place NULL values at the beginning or end of the result set by default. Some databases also support explicit NULLS FIRST and NULLS LAST options.

SELECT Name, Marks
FROM Students
ORDER BY Marks DESC NULLS LAST;

📊 Execution Order in a Query

Although ORDER BY appears near the end of an SQL statement, it is applied after the database has selected, filtered, grouped, and processed the data.

💼 Real-World Example

Consider a university portal that displays the highest-scoring students in the Computer Science department. The administrator wants the students listed from the highest marks to the lowest.

SELECT StudentID,
       Name,
       Marks
FROM Students
WHERE Department = 'Computer Science'
ORDER BY Marks DESC;

⚠️ Best Practices

🚀 Key Points to Remember