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?

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: