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;

FYI: It's generally considered bad practice to have an ORDER BY in views, unless you can guarantee the view will only ever be used for presentation. It's a waste because the ORDER BY will occur 2x if you use the view in another query with an ORDER BY. — OMG Ponies, Aug 10 '10 at 00:27
@OMG Ponies: it's generally considered illegal to have an `ORDER BY` in a `VIEW`. Standard SQL does not allow it. SQL Server has outlawed it since 2005. For SQL implementation that do allow it the behaviour is largely undocumented and counter intuitive. In other words, definitely to be avoided. — onedaywhen, Aug 10 '10 at 07:19
@onedaywhen: You're preaching to the choir, but MySQL [permits ORDER BY in views](http://dev.mysql.com/doc/refman/5.0/en/create-view.html), as does Oracle IME. SQL Server allows an ORDER BY if `TOP` is present, and SSMS loves to add `TOP 100 PERCENT`. — OMG Ponies, Aug 10 '10 at 16:00
@OMG Ponies@ "MySQL permits ORDER BY in views" -- MySQL permits a `CHECK` constraint in a `CREATE TABLE` but it doesn't actually honour it -- it never actually gets checked! The question is, do these SQL products always *honour* the `ORDER BY` in views e.g. when used in a query that also has an `ORDER BY` does it get sorted twice? Do they even document the behaviour or do you have to look the the execution plans to work it out? I think we know the answers ;) — onedaywhen, Aug 11 '10 at 08:23
FYI Just discovered another use for `ORDER BY 1`...using a cross-apply trick where you want a blank alias. Unfortunately the code e.g. is too big for a comment so I've posted it as an answer below FYI. — AndrewD, Sep 01 '16 at 06:00

score 279 · Accepted Answer · answered Aug 10 '10 at 00:17

279

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

answered Aug 10 '10 at 00:17

OMG Ponies

325,700
82
523
502

2

My question asking if there was an upside to using Ordinals: http://stackoverflow.com/questions/2253040/benefits-of-using-sql-ordinal-position-notation – OMG Ponies Aug 10 '10 at 00:23
This only has the `sql` tag. In Standard SQL only column correlation names are allowed in the `OREDER BY` clause because, in theory, the table correlation names are out of scope i.e. should be `ORDER BY PAYMENT_DATE;`. Of course, not all SQL implementations conform to Standards. – onedaywhen Aug 10 '10 at 07:17
Tested and working in SQL Server `;WITH cte AS( SELECT 1 AS Col1, 'z' AS Col2 UNION SELECT 2 AS Col1, 'y' AS Col2 UNION SELECT 3 AS Col1, 'x' AS Col2 ) SELECT Col2, Col1 FROM cte ORDER BY 1` – Aug 27 '18 at 20:20
@OMG Ponies, you mentioned that it's not a recommended practise, so what will be the next best replacement? asking cuz I'm curious.. thanks! – dian jin Jul 14 '20 at 05:53

score 53 · Answer 2 · edited May 29 '11 at 08:15

53

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

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

edited May 29 '11 at 08:15

Lazer

90,700
113
281
364

answered Aug 10 '10 at 10:51

daven11

2,905
3
26
43

9

Aha, that makes sense. This is the first good reason I've seen so far. – echo Aug 10 '10 at 15:00
4

@Lazer I don't believe so, to perform a union it probably does a sort internally, but this is an implementation question as opposed to a logical output question and in the spirit of SQL there is no need to output the rows in order. Also what if you wish to sort descending? Then you're back to the original problem. – daven11 Jun 01 '11 at 01:29
4

still... I would rather use `order by tablea.cola` – Shahar Shokrani Oct 09 '18 at 07:09
2

@ShaharShokrani that would not work. but you can say, I prefer select cola as x from tablea union select colb as x from tableb order by x; – Ozgur Ozturk Mar 22 '19 at 18:14
select * from (select cola col from tablea union select colb col from tableb) order by col – hareluya86 Jun 19 '20 at 07:28

score 26 · Answer 3 · answered Feb 09 '16 at 06:06

26

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

answered Feb 09 '16 at 06:06

kshitij

281
3
2

score 8 · Answer 4 · answered Aug 10 '10 at 00:17

8

I believe in Oracle it means order by column #1

answered Aug 10 '10 at 00:17

Christoph

4,251
3
24
38

score 7 · Answer 5 · answered Aug 10 '10 at 00:17

7

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

answered Aug 10 '10 at 00:17

CTKeane

674
1
9
22

score 3 · Answer 6 · answered Sep 13 '16 at 11:36

3

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

answered Sep 13 '16 at 11:36

nicV

650
7
16

1

why would you do that? why not alias them. [too late comment though] – abdul qayyum Jan 21 '18 at 15:57
1

@abdulqayyum it's just another way of doing things really. The above example is very simplified. Sometimes the 'Name' column is actually different columns from different tables that you are inserting into another table. Adding a bunch of aliases can make reading more difficult. Another example of where it is used is when selecting many different calculations & want to order by one - without needing to alias. (Although here I would personally prefer an alias to say what the calculation is) – nicV Jan 23 '18 at 07:51

score 0 · Answer 7 · answered Aug 20 '21 at 06:46

0

ORDER BY 1 means order by 1st column of the result set

answered Aug 20 '21 at 06:46

Tharindha

29
1

1

I do not see how this contributes anything new. Please explain the additional insight which your post provides. – Yunnosch Aug 25 '21 at 18:43

score 0 · Answer 8 · answered Jul 20 '22 at 04:34

SELECT case when b.salary is null then a.employee_id else b.employee_id end employee_id FROM Employees a FULL JOIN Salaries b on b. employee_id = a.employee_id WHERE b.salary is null or a.employee_id is null order by 1

it simply means sorting the view or table by 1st column of the query's result. above this query, the 'employee_id' column is the first column but this column is selected from two tables that's why to use this type of sorting.

kriss · Answer 9 · 2015-10-18T17:46:07.857

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.

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

9 Answers9

Linked