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; 8 Answers
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:
- It's not obvious/explicit
- If the column order changes, the query is still valid so you risk ordering by something you didn't intend
This is useful when you use set based operators e.g. union
select cola from tablea union select colb from tableb order by 1; it simply means sorting the view or table by 1st column of query's result.
0I believe in Oracle it means order by column #1
This will sort your results by the first column returned. In the example it will sort by payment_date.
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 ORDER BY 1 means order by 1st column of the result set
1An 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.
