What does ORDER BY 5 DESC mean?

Question

SELECT Departamentos.Nome_Dep,  
       Funcionarios.Nome AS Funcionario,
       Funcionarios.Salario,
       AVG(Funcionarios.Salario) OVER(PARTITION BY Departamentos.Nome_Dep) "Média por Departamento"
       Salario - AVG(Funcionarios.Salario) OVER(PARTITION BY Departamentos.Nome_Dep) "Diferença de Salário"   FROM Funcionarios
INNER JOIN Departamentos
    ON Funcionarios.ID_Dep = Departamentos.ID
ORDER BY 5 DESC

The Order By 5 is throwing me off. I've never anything like it. Order By [colunmname] yes, but Order By [number], never seen before. I pulled this off an article.

Note: This is T-SQL.

Source: Window Functions in SQL Server 2005, 2008, 2012

NOte that this is normally not a good practice as someone could easily add a column and change the ordering without noticing, I suspect it is done in this case however because column 5 is calculated column and they didn't want to go to the trouble of repeating the calulation in the ordering. BTW there is a missing comma between columns 4 and 5 — HLGEM, Nov 02 '11 at 18:45

score 34 · Accepted Answer · answered Nov 02 '11 at 17:42

34

This will order by the 5th field in this SELECT statement

answered Nov 02 '11 at 17:42

Cory

12,404
7
33
28

1

Excellent. Thank you. I immediately saw it in the article after looking at your answer. Thank you. – dotnetN00b Nov 02 '11 at 17:44

score 6 · Answer 2 · answered Nov 02 '11 at 17:43

6

Order by the 5th column in the result set.

answered Nov 02 '11 at 17:43

Jonathan Allen

68,373
70
259
447

score 4 · Answer 3 · answered Nov 02 '11 at 17:43

4

The number represents the index of the column within your select list.

Source: http://mattberseth.com/blog/2007/05/quick_tip_order_by_1_descendin.html

answered Nov 02 '11 at 17:43

Polynomial

27,674
12
80
107

score 4 · Answer 4 · answered Nov 02 '11 at 17:44

4

Order by fifth column in the result set descending.

answered Nov 02 '11 at 17:44

Mikael Eriksson

136,425
22
210
281

score 2 · Answer 5 · answered Jun 10 '15 at 11:04

It is the SORTING BY RELATIVE POSITION.

You can use the SQL ORDER BY clause to sort by relative position in the result set, where the first field in the result set is 1. The next field is 2, and so on. Here in this case Order by 5th field in the result set.

Go through http://www.techonthenet.com/sql/order_by.php about sql order by.

score 1 · Answer 6 · answered May 03 '18 at 15:48

Useful when a similar column name has become a calcuated output field,

In the following example, it would be confusing if say 'order by numberofVioation' as this column name has just become the name of a query output SUM field (of the same old column data)

So it become useful in calculated output field

Example:

Original fields:

/* now add an extra Sum of Violation for each type, pls note 'order by 2 desc' refers to order by the 2nd queried column, ie hi to low */

select Type, sum(numberOfViolations) as numberOfViolations from restaurantsTable group by Type order by 2 desc

score 0 · Answer 7 · answered Jan 27 '15 at 11:57

0

Order by 5th field in the result set.

answered Jan 27 '15 at 11:57

NitzyJ

1
1

What does ORDER BY 5 DESC mean?

7 Answers7