PostgreSQL Order By Two Columns with Nullable Last

=====================================================

In this article, we will explore how to order rows from a PostgreSQL table by two columns: date and bonus. The twist is that the last column should be ordered based on whether its value is nullable or not. In other words, we want to prioritize non-nullable bonuses over nullable ones when sorting.

Understanding the Problem

The problem statement involves ordering rows in a PostgreSQL table based on two columns: date and bonus. The catch is that the last column should be ordered first if it’s non-nullable, and then by date. We’ll break down this concept further with an example table and its corresponding query.

Example Table

Let’s take a look at the following table:

Querying the Table

We want to order rows by date and bonus. The order should be as follows:

1. First, we sort non-nullable bonuses in descending order.
2. Then, we sort nullable bonuses in ascending order.

To achieve this, we can use the following PostgreSQL query:

SELECT * 
FROM table
ORDER BY
    bonus IS NULL ASC,
    date DESC;

Let’s break down this query further to understand how it works.

bonus IS NULL Condition

In PostgreSQL, a nullable column is considered as NULL by default. When we check the value of a column using the IS NULL condition, we get:

• TRUE if the value is exactly NULL.
• FALSE otherwise (i.e., non-nullable values).

This allows us to prioritize non-nullable bonuses over nullable ones.

Sorting Non-Nullable Bonuses

We want to sort non-nullable bonuses in descending order. To achieve this, we simply use the DESC keyword in our query:

SELECT * 
FROM table
ORDER BY bonus DESC;

This sorts the rows with non-nullable bonuses in descending order based on their values.

Sorting Nullable Bonuses

Once the non-nullable bonuses are sorted, we want to sort the nullable bonuses in ascending order. We achieve this by adding another condition to our query:

SELECT * 
FROM table
ORDER BY bonus IS NULL ASC,
    date DESC;

This ensures that the rows with nullable bonuses are ordered first based on their values, followed by the non-nullable rows.

Using CASE Statements

Some readers might be more comfortable using CASE statements instead of the IS NULL condition. While both approaches work, there’s a slight difference in syntax and behavior:

• bonus IS NULL checks for exact NULL values.
• CASE WHEN bonus IS NOT NULL THEN ... END checks for non-nullable values.

Here’s an equivalent query using CASE statements:

SELECT * 
FROM table
ORDER BY
    CASE WHEN bonus IS NOT NULL THEN 1 ELSE 0 END DESC,
    date DESC;

This achieves the same result as our original query but uses a different syntax to check for non-nullable values.

Conclusion

In this article, we explored how to order rows from a PostgreSQL table by two columns: date and bonus. We prioritized non-nullable bonuses over nullable ones when sorting. The query uses the IS NULL condition or CASE statements to achieve this. By understanding the syntax and behavior of these conditions, you can write efficient queries to sort your tables according to specific criteria.

Additional Resources

• PostgreSQL documentation: [Querying Data](https://www.postgresql.org/docs/current/tutorial querying.html)
• PostgreSQL documentation: Conditional expressions

By following this article and exploring the additional resources, you’ll be well-equipped to tackle complex data sorting tasks in PostgreSQL.

Last modified on 2025-02-24