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

Question

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 1

See http://mattberseth.com/blog/2007/05/quick_tip_order_by_1_descendin.html .

Answer 2

I believe in Oracle it means order by column #1

Answer 3

This will sort your results by the first column returned. In the example it will sort by payment_date.

Answer 4

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

Answer 5

Also see:

http://www.techonthenet.com/sql/order_by.php

For a description of order by. I learned something! :)

I've also used this in the past when I wanted to add an indeterminate number of filters to a sql statement. Sloppy I know, but it worked. :P