The Purpose and Risks of ORDER BY 1 in SQL Statements

Ordinal Notation in SQL ORDER BY Clause

In SQL query language, the ORDER BY clause is used to sort result sets. Beyond the conventional column name sorting, SQL supports ordinal-based sorting using column positions, such as ORDER BY 1. While this syntax offers brevity, it requires careful consideration in practical development.

Mechanism of Ordinal-Based Sorting

The number 1 in ORDER BY 1 refers to the position of the first column in the SELECT clause. In the provided view definition example:

CREATE VIEW v_payment_summary AS
SELECT A.PAYMENT_DATE,
       (SELECT SUM(paymentamount)
          FROM payment B
         WHERE PAYMENT_DATE = B.PAYMENT_DATE
           AND SOME_CONDITION) AS SUM_X,
       (SELECT SUM(paymentamount)
          FROM payment B
         WHERE PAYMENT_DATE = B.PAYMENT_DATE
           AND SOME_OTHER_CONDITION) AS SUM_Y    
FROM payment A    
ORDER BY 1;

Here, ORDER BY 1 is equivalent to ORDER BY A.PAYMENT_DATE, since PAYMENT_DATE is the first column in the SELECT list. This ordinal notation references columns by their position in the SELECT statement rather than by name.

Potential Risks of Ordinal Sorting

Despite its conciseness, ORDER BY 1 poses significant risks in enterprise application development:

Readability Concerns: For code maintainers, ORDER BY 1 is less intuitive than ORDER BY PAYMENT_DATE. Other developers reading the code must mentally calculate column positions to understand the sorting logic, increasing cognitive load.

Refactoring Dangers: If the SELECT clause column order is modified later, such as adding a new column before PAYMENT_DATE:

SELECT 
    CURRENT_TIMESTAMP AS EXECUTION_TIME,
    A.PAYMENT_DATE,
    SUM_X,
    SUM_Y

The ORDER BY 1 would then sort by EXECUTION_TIME instead of the original PAYMENT_DATE. This implicit dependency can introduce hard-to-detect errors during code changes.

Best Practice Recommendations

Based on this analysis, the following practices are recommended in SQL development:

Prefer explicit column names for sorting: ORDER BY PAYMENT_DATE. This approach clearly expresses sorting intent and avoids implicit dependencies on column positions.

Particular attention should be paid to sorting stability in view definitions. If sorting is essential to business logic, explicit column name references should be used.

For complex queries, consider using column aliases to enhance readability:

SELECT 
    A.PAYMENT_DATE AS payment_date,
    SUM_X AS total_amount_x,
    SUM_Y AS total_amount_y
ORDER BY payment_date

This approach maintains code clarity while avoiding the risks associated with ordinal notation.

Scenario Analysis

While not recommended for regular use, ORDER BY 1 retains value in specific contexts:

During rapid prototyping phases when query structures require frequent adjustments, ordinals can simplify code modifications.

For temporary data analysis queries, particularly when column names are lengthy or complex, ordinal notation can offer some convenience.

However, in formal production code, especially in views and stored procedures requiring long-term maintenance, explicit column name sorting should always be preferred.