You can use an alias for any field or aggregated field in a SELECT statement in a SOQL query. Use a field alias to identify the field when you're processing the query results in your code. Specify the alias directly after the associated field.
And the answer is yes it does still apply.
In Oracle you cannot use an alias in a group by clause.
Due to logical query processing order, alias can be used in order by.
SQL is implemented as if a query was executed in the following order:
For most relational database systems, this order explains which names (columns or aliases) are valid because they must have been introduced in a previous step.
So in Oracle and SQL Server, you cannot use a term in the GROUP BY clause that you define in the SELECT clause because the GROUP BY is executed before the SELECT clause.
There are exceptions though: MySQL and Postgres seem to have additional smartness that allows it.
You could always use a subquery so you can use the alias; Of course, check the performance (Possible the db server will run both the same, but never hurts to verify):
SELECT ItemName, FirstLetter, COUNT(ItemName)
FROM (
SELECT ItemName, SUBSTRING(ItemName, 1, 1) AS FirstLetter
FROM table1
) ItemNames
GROUP BY ItemName, FirstLetter
At least in PostgreSQL you can use the column number in the resultset in your GROUP BY clause:
SELECT
itemName as ItemName,
substring(itemName, 1,1) as FirstLetter,
Count(itemName)
FROM table1
GROUP BY 1, 2
Of course this starts to be a pain if you are doing this interactively and you edit the query to change the number or order of columns in the result. But still.
SQL Server doesn't allow you to reference the alias in the GROUP BY clause because of the logical order of processing. The GROUP BY clause is processed before the SELECT clause, so the alias is not known when the GROUP BY clause is evaluated. This also explains why you can use the alias in the ORDER BY clause.
Here is one source for information on the SQL Server logical processing phases.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With