Difference between order by column name and order by(nos

Parasakthi · New User Joined: 14 Feb 2008 Posts: 1 Location: Bangalore

Hi all,

Where we have to use order by 1,2 clause?

What is the difference between order by column name and order by(nos)..

vasanthkumarhb · Posted: Fri Feb 22, 2008 1:15 pm

Hi......

GROUP BY class concentrates on fetching data row wise, where as ORDER BY specifies fetching the data in column wise.

consider one example query for analysis.

SELECT DEPT,COUNT(*) FROM STAFF GROUP BY DEPT HAVING MAX(COMM)>5000 ORDER BY 2 DESC

Here query considering on the 2nd column that is DEPT and the maximum of commission and grouped together after satisfying HAVING condition.

If u want to operate on the particular column or particular group u can give ORDER BY 2(2nd column) DESC or GROUP BY 3(3rd row) ASC(default)

stodolas · Posted: Fri Feb 22, 2008 6:42 pm

Did you even read the O/P? They didn't ask anything about GROUP BY.

Parasakthi: The syntax of ORDER BY allows you to refer to columns either by name or by position. Both are valid references and give them same end result.

Phrzby Phil · Posted: Fri Feb 22, 2008 6:57 pm

But of course the danger in using numbers is that they must be adjusted if someone later modifies the SELECT list, and names are more meangful.

However, if you have a bunch of UNIONs, the col names most likely differ, so I think you must use numbers.

acevedo · Active User Joined: 11 May 2005 Posts: 344 Location: Spain

but sometimes you need to use NUMBERS becouse the field you need to order by is not a column but a function, for example, column3+column4-column5...

Phrzby Phil · Posted: Fri Feb 22, 2008 7:50 pm

Can't you give a name to a SELECT expression?

acevedo · Active User Joined: 11 May 2005 Posts: 344 Location: Spain

I cannot test it now BUT I think you cannot use that name in order by... can anyone try it?