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

User · Accepted 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    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

User · Answer

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

User · Answer

This will sort your results by the first column returned   In the example it will sort by payment date

User · Answer

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

User · Answer

An example here from a sample test WAMP server database -  mysql gt  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 gt  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

User · Answer

This is useful when you use set based operators e g  union  select cola   from tablea union select colb   from tableb order by 1

User · Answer

I believe in Oracle it means order by column  1

User · Answer

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

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

Examples related to sql

Examples related to sql-order-by