CODEWITHSUNDEEP
sqloracle11gsql-order-by
sql_dummy
sql_dummy

Reputation: 61

Why following queries have different output?

Even though 2*3-5 = 1 why do both these queries have different output?

SELECT * FROM table ORDER BY 1 asc, column2 desc;

vs.

SELECT * FROM table ORDER BY 2*3-5 asc, column2 desc;

Upvotes: 2

Views: 45

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

Sort by first column in resultset and then by column2

SELECT * FROM table ORDER BY 1 asc, column2 desc;

Sort by constant expression and column2:

SELECT * FROM table ORDER BY 2*3-5 asc, column2 desc;
<=>
SELECT * FROM table ORDER BY column2 desc;

SqlFiddleDemo

ORDER BY { column-Name | ColumnPosition | Expression }

ColumnPosition

An integer that identifies the number of the column in the SelectItems in the underlying query of the SELECT statement. ColumnPosition must be greater than 0 and not greater than the number of columns in the result table. In other words, if you want to order by a column, that column must be specified in the SELECT list.

Keep in mind that in some RDBMS ordering by constant is not allowed like SQL Server:

SELECT * 
FROM tab
ORDER BY 2*3-5 asc, column2 desc;
-- A constant expression was encountered in the ORDER BY list, position 1.

Upvotes: 2

Related Questions