Q

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:

  1. the 4th column, i.e. manager_id
  2. the 2nd column, i.e. hire_date
  3. 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.

This was first published in October 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close