For this query:
select first_name, hire_date, salary, manager_id mid from employees where department_id in(110,100) order by 4,2,3
My question is: what does the last line "order by 4,2,3" mean?
Those numbers refer to the ordinal position of the columns within the result set that determine the sequence of the result set. The ORDER BY sequence is:
- the 4th column, i.e. manager_id
- the 2nd column, i.e. hire_date
- the 3rd column, i.e. salary
Each of these will be in ascending sequence, since that is the default.
Admittedly, it's easier to type order by 4,2,3 than it is to type order by manager_id, hire_date, salary. However, since this is the age of cut 'n' paste, it shouldn't be too hard to construct the ORDER BY clause by pasting column names from the SELECT clause.
The reason you want to spell out, by name, which columns to sort by, is to make the query more understandable to someone not familiar with what the query is doing. Remember, this someone will be you, six months down the road, after you have forgotten why it was 4,2,3 and not 2,4,3. If the query uses ordinal position numbers, you are forced to read the SELECT clause and count columns, which can be daunting if they include expressions as well as simple column names.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading