[sql] 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;

This question is related to sql sql-order-by

The answer is


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


This is useful when you use set based operators e.g. union

select cola
  from tablea
union
select colb
  from tableb
order by 1;

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


it simply means sorting the view or table by 1st column of query's result.


As mentioned in other answers ORDER BY 1 orders by the first column.

I came across another example of where you might use it though. We have certain queries which need to be ordered select the same column. You would get a SQL error if ordering by Name in the below.

SELECT Name, Name FROM Segment ORDER BY 1

An example here from a sample test WAMP server database:-

mysql> select * from user_privileges;

| GRANTEE            | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
   +--------------------+---------------+-------------------------+--------------+
| 'root'@'localhost' | def           | SELECT                  | YES          |
| 'root'@'localhost' | def           | INSERT                  | YES          |
| 'root'@'localhost' | def           | UPDATE                  | YES          |
| 'root'@'localhost' | def           | DELETE                  | YES          |
| 'root'@'localhost' | def           | CREATE                  | YES          |
| 'root'@'localhost' | def           | DROP                    | YES          |
| 'root'@'localhost' | def           | RELOAD                  | YES          |
| 'root'@'localhost' | def           | SHUTDOWN                | YES          |
| 'root'@'localhost' | def           | PROCESS                 | YES          |
| 'root'@'localhost' | def           | FILE                    | YES          |
| 'root'@'localhost' | def           | REFERENCES              | YES          |
| 'root'@'localhost' | def           | INDEX                   | YES          |
| 'root'@'localhost' | def           | ALTER                   | YES          |
| 'root'@'localhost' | def           | SHOW DATABASES          | YES          |
| 'root'@'localhost' | def           | SUPER                   | YES          |
| 'root'@'localhost' | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost' | def           | LOCK TABLES             | YES          |
| 'root'@'localhost' | def           | EXECUTE                 | YES          |
| 'root'@'localhost' | def           | REPLICATION SLAVE       | YES          |
| 'root'@'localhost' | def           | REPLICATION CLIENT      | YES          |
| 'root'@'localhost' | def           | CREATE VIEW             | YES          |
| 'root'@'localhost' | def           | SHOW VIEW               | YES          |
| 'root'@'localhost' | def           | CREATE ROUTINE          | YES          |
| 'root'@'localhost' | def           | ALTER ROUTINE           | YES          |
| 'root'@'localhost' | def           | CREATE USER             | YES          |
| 'root'@'localhost' | def           | EVENT                   | YES          |
| 'root'@'localhost' | def           | TRIGGER                 | YES          |
| 'root'@'localhost' | def           | CREATE TABLESPACE       | YES          |
| 'root'@'127.0.0.1' | def           | SELECT                  | YES          |
| 'root'@'127.0.0.1' | def           | INSERT                  | YES          |
| 'root'@'127.0.0.1' | def           | UPDATE                  | YES          |
| 'root'@'127.0.0.1' | def           | DELETE                  | YES          |
| 'root'@'127.0.0.1' | def           | CREATE                  | YES          |
| 'root'@'127.0.0.1' | def           | DROP                    | YES          |
| 'root'@'127.0.0.1' | def           | RELOAD                  | YES          |
| 'root'@'127.0.0.1' | def           | SHUTDOWN                | YES          |
| 'root'@'127.0.0.1' | def           | PROCESS                 | YES          |
| 'root'@'127.0.0.1' | def           | FILE                    | YES          |
| 'root'@'127.0.0.1' | def           | REFERENCES              | YES          |
| 'root'@'127.0.0.1' | def           | INDEX                   | YES          |
| 'root'@'127.0.0.1' | def           | ALTER                   | YES          |
| 'root'@'127.0.0.1' | def           | SHOW DATABASES          | YES          |
| 'root'@'127.0.0.1' | def           | SUPER                   | YES          |
| 'root'@'127.0.0.1' | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'127.0.0.1' | def           | LOCK TABLES             | YES          |
| 'root'@'127.0.0.1' | def           | EXECUTE                 | YES          |
| 'root'@'127.0.0.1' | def           | REPLICATION SLAVE       | YES          |
| 'root'@'127.0.0.1' | def           | REPLICATION CLIENT      | YES          |
| 'root'@'127.0.0.1' | def           | CREATE VIEW             | YES          |
| 'root'@'127.0.0.1' | def           | SHOW VIEW               | YES          |
| 'root'@'127.0.0.1' | def           | CREATE ROUTINE          | YES          |
| 'root'@'127.0.0.1' | def           | ALTER ROUTINE           | YES          |
| 'root'@'127.0.0.1' | def           | CREATE USER             | YES          |
| 'root'@'127.0.0.1' | def           | EVENT                   | YES          |
| 'root'@'127.0.0.1' | def           | TRIGGER                 | YES          |
| 'root'@'127.0.0.1' | def           | CREATE TABLESPACE       | YES          |
| 'root'@'::1'       | def           | SELECT                  | YES          |
| 'root'@'::1'       | def           | INSERT                  | YES          |
| 'root'@'::1'       | def           | UPDATE                  | YES          |
| 'root'@'::1'       | def           | DELETE                  | YES          |
| 'root'@'::1'       | def           | CREATE                  | YES          |
| 'root'@'::1'       | def           | DROP                    | YES          |
| 'root'@'::1'       | def           | RELOAD                  | YES          |
| 'root'@'::1'       | def           | SHUTDOWN                | YES          |
| 'root'@'::1'       | def           | PROCESS                 | YES          |
| 'root'@'::1'       | def           | FILE                    | YES          |
| 'root'@'::1'       | def           | REFERENCES              | YES          |
| 'root'@'::1'       | def           | INDEX                   | YES          |
| 'root'@'::1'       | def           | ALTER                   | YES          |
| 'root'@'::1'       | def           | SHOW DATABASES          | YES          |
| 'root'@'::1'       | def           | SUPER                   | YES          |
| 'root'@'::1'       | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'::1'       | def           | LOCK TABLES             | YES          |
| 'root'@'::1'       | def           | EXECUTE                 | YES          |
| 'root'@'::1'       | def           | REPLICATION SLAVE       | YES          |
| 'root'@'::1'       | def           | REPLICATION CLIENT      | YES          |
| 'root'@'::1'       | def           | CREATE VIEW             | YES          |
| 'root'@'::1'       | def           | SHOW VIEW               | YES          |
| 'root'@'::1'       | def           | CREATE ROUTINE          | YES          |
| 'root'@'::1'       | def           | ALTER ROUTINE           | YES          |
| 'root'@'::1'       | def           | CREATE USER             | YES          |
| 'root'@'::1'       | def           | EVENT                   | YES          |
| 'root'@'::1'       | def           | TRIGGER                 | YES          |
| 'root'@'::1'       | def           | CREATE TABLESPACE       | YES          |
| ''@'localhost'     | def           | USAGE                   | NO           |
+--------------------+---------------+-------------------------+--------------+
85 rows in set (0.00 sec)

And when it is given additional order by PRIVILEGE_TYPE or can be given order by 3 . Notice the 3rd column (PRIVILEGE_TYPE) getting sorted alphabetically.

mysql> select * from user_privileges order by PRIVILEGE_TYPE;
+--------------------+---------------+-------------------------+--------------+
| GRANTEE            | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+--------------------+---------------+-------------------------+--------------+
| 'root'@'127.0.0.1' | def           | ALTER                   | YES          |
| 'root'@'::1'       | def           | ALTER                   | YES          |
| 'root'@'localhost' | def           | ALTER                   | YES          |
| 'root'@'::1'       | def           | ALTER ROUTINE           | YES          |
| 'root'@'localhost' | def           | ALTER ROUTINE           | YES          |
| 'root'@'127.0.0.1' | def           | ALTER ROUTINE           | YES          |
| 'root'@'127.0.0.1' | def           | CREATE                  | YES          |
| 'root'@'::1'       | def           | CREATE                  | YES          |
| 'root'@'localhost' | def           | CREATE                  | YES          |
| 'root'@'::1'       | def           | CREATE ROUTINE          | YES          |
| 'root'@'localhost' | def           | CREATE ROUTINE          | YES          |
| 'root'@'127.0.0.1' | def           | CREATE ROUTINE          | YES          |
| 'root'@'::1'       | def           | CREATE TABLESPACE       | YES          |
| 'root'@'localhost' | def           | CREATE TABLESPACE       | YES          |
| 'root'@'127.0.0.1' | def           | CREATE TABLESPACE       | YES          |
| 'root'@'::1'       | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost' | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'127.0.0.1' | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost' | def           | CREATE USER             | YES          |
| 'root'@'127.0.0.1' | def           | CREATE USER             | YES          |
| 'root'@'::1'       | def           | CREATE USER             | YES          |
| 'root'@'localhost' | def           | CREATE VIEW             | YES          |
| 'root'@'127.0.0.1' | def           | CREATE VIEW             | YES          |
| 'root'@'::1'       | def           | CREATE VIEW             | YES          |
| 'root'@'127.0.0.1' | def           | DELETE                  | YES          |
| 'root'@'::1'       | def           | DELETE                  | YES          |
| 'root'@'localhost' | def           | DELETE                  | YES          |
| 'root'@'::1'       | def           | DROP                    | YES          |
| 'root'@'localhost' | def           | DROP                    | YES          |
| 'root'@'127.0.0.1' | def           | DROP                    | YES          |
| 'root'@'127.0.0.1' | def           | EVENT                   | YES          |
| 'root'@'::1'       | def           | EVENT                   | YES          |
| 'root'@'localhost' | def           | EVENT                   | YES          |
| 'root'@'127.0.0.1' | def           | EXECUTE                 | YES          |
| 'root'@'::1'       | def           | EXECUTE                 | YES          |
| 'root'@'localhost' | def           | EXECUTE                 | YES          |
| 'root'@'127.0.0.1' | def           | FILE                    | YES          |
| 'root'@'::1'       | def           | FILE                    | YES          |
| 'root'@'localhost' | def           | FILE                    | YES          |
| 'root'@'localhost' | def           | INDEX                   | YES          |
| 'root'@'127.0.0.1' | def           | INDEX                   | YES          |
| 'root'@'::1'       | def           | INDEX                   | YES          |
| 'root'@'::1'       | def           | INSERT                  | YES          |
| 'root'@'localhost' | def           | INSERT                  | YES          |
| 'root'@'127.0.0.1' | def           | INSERT                  | YES          |
| 'root'@'127.0.0.1' | def           | LOCK TABLES             | YES          |
| 'root'@'::1'       | def           | LOCK TABLES             | YES          |
| 'root'@'localhost' | def           | LOCK TABLES             | YES          |
| 'root'@'127.0.0.1' | def           | PROCESS                 | YES          |
| 'root'@'::1'       | def           | PROCESS                 | YES          |
| 'root'@'localhost' | def           | PROCESS                 | YES          |
| 'root'@'::1'       | def           | REFERENCES              | YES          |
| 'root'@'localhost' | def           | REFERENCES              | YES          |
| 'root'@'127.0.0.1' | def           | REFERENCES              | YES          |
| 'root'@'::1'       | def           | RELOAD                  | YES          |
| 'root'@'localhost' | def           | RELOAD                  | YES          |
| 'root'@'127.0.0.1' | def           | RELOAD                  | YES          |
| 'root'@'::1'       | def           | REPLICATION CLIENT      | YES          |
| 'root'@'localhost' | def           | REPLICATION CLIENT      | YES          |
| 'root'@'127.0.0.1' | def           | REPLICATION CLIENT      | YES          |
| 'root'@'::1'       | def           | REPLICATION SLAVE       | YES          |
| 'root'@'localhost' | def           | REPLICATION SLAVE       | YES          |
| 'root'@'127.0.0.1' | def           | REPLICATION SLAVE       | YES          |
| 'root'@'127.0.0.1' | def           | SELECT                  | YES          |
| 'root'@'::1'       | def           | SELECT                  | YES          |
| 'root'@'localhost' | def           | SELECT                  | YES          |
| 'root'@'127.0.0.1' | def           | SHOW DATABASES          |  YES          |
| 'root'@'::1'       | def           | SHOW DATABASES          | YES          |
| 'root'@'localhost' | def           | SHOW DATABASES          | YES          |
| 'root'@'127.0.0.1' | def           | SHOW VIEW               | YES          |
| 'root'@'::1'       | def           | SHOW VIEW               | YES          |
| 'root'@'localhost' | def           | SHOW VIEW               | YES          |
| 'root'@'localhost' | def           | SHUTDOWN                | YES          |
| 'root'@'127.0.0.1' | def           | SHUTDOWN                | YES          |
| 'root'@'::1'       | def           | SHUTDOWN                | YES          |
| 'root'@'::1'       | def           | SUPER                   | YES          |
| 'root'@'localhost' | def           | SUPER                   | YES          |
| 'root'@'127.0.0.1' | def           | SUPER                   | YES          |
| 'root'@'127.0.0.1' | def           | TRIGGER                 | YES          |
| 'root'@'::1'       | def           | TRIGGER                 | YES          |
| 'root'@'localhost' | def           | TRIGGER                 | YES          |
| 'root'@'::1'       | def           | UPDATE                  | YES          |
| 'root'@'localhost' | def           | UPDATE                  | YES          |
| 'root'@'127.0.0.1' | def           | UPDATE                  | YES          |
| ''@'localhost'     | def           | USAGE                   | NO           |     +--------------------+---------------+-------------------------+--------------+
85 rows in set (0.00 sec)

DEFINITIVELY, a long answer and alot of scrolling. Also I struggled hard to pass the output of the queries to a text file. Here is how to do that without using the annoying into outfile thing-

tee E:/sqllogfile.txt;

And when you are done, stop the logging-

tee off;

Hope it adds more clarity.


I believe in Oracle it means order by column #1