PostgreSQL – ORDER BY Clause

The ORDER BY clause in PostgreSQL is used to sort the rows of a query result by one or more columns in ascending or descending order.

In this tutorial, we will guide you step-by-step on using the ORDER BY clause, starting with creating a database and table, inserting data, and then demonstrating various ORDER BY scenarios.

Step 1: Create a Database

First, create a database named school to hold the data.

</>
Copy
CREATE DATABASE school;

Connect to the database:

</>
Copy
\c school;

Step 2: Create a Table

Next, create a table named students to store information about students.

</>
Copy
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    grade CHAR(1),
    score DECIMAL(5, 2)
);

Step 3: Insert Data

Insert some sample data into the students table.

</>
Copy
INSERT INTO students (name, age, grade, score)
VALUES 
('Alice', 14, 'A', 95.5),
('Bob', 15, 'B', 85.3),
('Charlie', 14, 'A', 88.2),
('David', 15, 'C', 72.5),
('Eve', 14, 'B', 91.0);
PostgreSQL - ORDER BY Clause - Setup for examples

Step 4: Basic Usage of ORDER BY

Retrieve all student records sorted by their scores in ascending order:

</>
Copy
SELECT * FROM students
ORDER BY score ASC;

The result will display the students sorted from the lowest to the highest score.

PostgreSQL - ORDER BY Clause - Basic Usage

Step 5: Sort in Descending Order

To sort the students by score in descending order:

</>
Copy
SELECT * FROM students
ORDER BY score DESC;

This will list the students starting with the highest score.

PostgreSQL - ORDER BY Clause - Sort in Descending Order

Step 6: Sorting by Multiple Columns

You can sort by multiple columns. For example, to sort by grade in ascending order and then by score in descending order:

</>
Copy
SELECT * FROM students
ORDER BY grade ASC, score DESC;

This will group students by their grade and within each grade, sort them by their scores in descending order.

PostgreSQL - ORDER BY Clause - Sorting by Multiple Columns

Step 7: Sorting by Alias

Use a calculated column or alias for sorting. For example, sort by score and label it as performance:

</>
Copy
SELECT name, score AS performance
FROM students
ORDER BY performance DESC;

This query sorts the result using the alias performance.

PostgreSQL - ORDER BY Clause - Sorting by Alias

Step 8: NULLS FIRST or NULLS LAST

If the column contains NULL values, you can specify whether to display them first or last:

</>
Copy
SELECT * FROM students
ORDER BY score DESC NULLS LAST;

This ensures that rows with NULL values in the score column appear at the end.

PostgreSQL - ORDER BY Clause - NULLS FIRST or NULLS LAST

Conclusion

The ORDER BY clause is a versatile feature in PostgreSQL that allows you to sort query results in various ways. Experiment with these scenarios to better understand its power and flexibility.