echo echo - 3 months ago 11
SQL Question

What is the purpose of Order By 1 in SQL select statement?

I'm reading through some old code at work, and have noticed that there are several views with an

order by 1
clause. What does this accomplish?

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;

Answer

This:

ORDER BY 1

...is known as an "Ordinal" - the number stands for the column based on the number of columns defined in the SELECT clause. In the query you provided, it means:

ORDER BY A.PAYMENT_DATE

It's not a recommended practice, because:

  1. It's not obvious/explicit
  2. If the column order changes, the query is still valid so you risk ordering by something you didn't intend
Comments