What are the differences between order by 1 and order by column name?
During my interview at a tech company I was asked about the function of “ORDER BY 1” in SQL and how it is different from “ORDER BY column name”. Provide me the answer and one illustration also.
In the context of SQL the “SQL order by 1” is used to sort the query results based on the first column in the SELECT clause. On the other hand, the ORDER BY clause is used in arranging the retrieved data in both ascending and descending order. The basis used by the order is specified columns or the positional indices of the data.
For example, you can consider a scenario where the “students” table with columns “ student_ I’d”, “name”, and “age” is here. To sort the results by the first column using its potential index:-
SELECT * FROM students
ORDER BY 1; -- Orders the results by the first column ( student_ I’d)This above structure provides the outputs on the first column in ascending and descending order. Using “ORDER BY 1” could be concise, especially when you are dealing with various numbers of columns, however, it can lack in readability sometimes.