Whats the difference? (1 Viewer)

[vbaInet]

Let me elaborate The_Doc_Man.

When a column name is given to to ORDER BY, it looks for the field in the tables listed in the FROM clause. If it doesn't find the field it will error.

When a column position is given to to ORDER BY, it looks for that position in the SELECT clause and if there are only 4 fields listed in SELECT, but you wrote a position of 5, it will error.

Here are some examples using column name:

Pass:

SELECT ID, Amount, Amount * Quantity AS Total
FROM tableA
ORDER BY Amount * Quantity

SELECT ID, Amount, Quantity
FROM table A
ORDER BY Amount * Quantity

... "Amount * Quantity" being the expression

Fail:

SELECT ID, Amount, Amount * Quantity AS Total, Total + 1 AS NetTotal
FROM tableA
ORDER BY Total + 1

SELECT ID, Amount, Amount * Quantity AS Total, Total + 1 AS NetTotal
FROM tableA
ORDER BY NetTotal

... both will fail because both aliases Total and NetTotal aren't fields in tableA. This is what prabha was doing. ORDER BY is looking for Total and NetTotal in tableA but these aren't fields in tableA.

Some examples using column position:

Pass:

SELECT ID, Amount, Amount * Quantity AS Total, Total + 1 AS NetTotal
FROM tableA
ORDER BY 4

... now ORDER BY just looks for the position and it finds it. It's now looking in the SELECT clause and starts counting the position from 1 until it gets to 4.

Fail:

SELECT ID, Amount
FROM tableA
ORDER BY 4

... column 4 doesn't exist and as a result it will fail.

I hope that helps.