ORDER BY ordinal position
For this query, what does the last line "order by 4,2,3" mean?
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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments