Using list alias in WHERE or GROUP BY clause

Question

Hello,

I'm writing a stored procedure containing SQL Select queries with some functions on some columns and a GROUP BY clause. Obviously, I have to repeat these functions in the GROUP BY, which dramatically decreases code readability and increases redundancy.

So far, an alternative I have found was to use a derived query so I can access the alias names defined in the "second-level" query from the "top-level" query. I was moderately happy with this workaround as it increases code complexity (and indentation) and I feel that I shouldn't have to use two queries when I can return all the data I want using a single one.

I've googled a bit and I found this page which returns the same tip about the subquery (+ one about creating a view but that's overkill in most cases).

http://databases.aspfaq.com/database/how-do-i-use-a-select-list-alias-in-the-where-or-group-by-clause.html

I can't seriously believe that in 2010, there is no other way, especially with all the focus on code reusability and readability that has been going on for some years: would anyone have an elegant tip I never heard ever heard of (for SQL Server 2005/2008), or have an educated guess at why things should still be done that way in 2010?

Thanks.

Anonymous.

Answer 1

You can use the CTEs (which can be nested and reused):

WITH    sub AS
        ( 
        SELECT  *, LastName + ', ' + FirstName AS FullName
        FROM    Employees 
        ),
        moresub AS
        (
        SELECT   *, Title + ' ' + FullName AS TitledName
        FROM     sub
        )
SELECT  FullName
FROM    moresub
WHERE   moresub.TitledName = 'Mr. Bertrand, Aaron'
UNION ALL
SELECT  FullName
FROM    moresub
WHERE   moresub.FullName = 'Doe, John'

Update:

Seems I misunderstood your question.

In MySQL, you can use the SELECT list aliases in ORDER BY, GROUP BY and HAVING clauses:

SELECT  id + 1 AS myalias
FROM    (
        SELECT  1 AS id
        UNION ALL
        SELECT  2 AS id
        ) q
GROUP BY
        myalias
HAVING  myalias <= 3
ORDER BY
        myalias DESC

, but MySQL is known for poor handling of the nested queries, so this ability is vital for performance.

Most probably that the other systems (which handle the nested queries and the CTEs quite well) just decided that they have more important things to implement.

Answer 2

There is a good explanation of why it is this way here:

http://stackoverflow.com/questions/2068682/why-cant-i-use-alias-in-a-count-column-and-reference-it-in-a-having-clause