Order by Alias Not Working

ORDER BY Alias not working

You can use ORDER BY 1 to order by the first field, which is "Fruits". The same is valid for GROUP BY

Update

For the order, instead of doing the case in the order by, create a new column in.. say.. the second position:

(CASE 
    WHEN "Fruits" = 'Apple' THEN 1 
    WHEN "Fruits" = 'Pear' THEN 2 
    WHEN "Fruits" = 'Grapes' THEN 3 
    ELSE 4 ) as Order

Then in you ORDER BY 2.

MSSQL CASE after ORDER BY not working with alias

You cannot use aliased columns in the ORDER BY clause because sorting logically it happens before the SELECT clause. You could use column numbers, e.g. ORDER BY 9 would sort the output by your 'Liefertermin' column, but that is considered a bad practice. Best way around it would be to put everything inside a subquery, like:

SELECT * FROM
(SELECT... -- your whole select here without the ORDER BY clause
 ) as t1
ORDER BY
    CASE WHEN @Sort = 1 then SortDate END DESC
    CASE WHEN @Sort = 2 then Liefertermin END DESC
    CASE WHEN @Sort = 3 then Name END DESC

ORDER BY clause is not sorting by alias in SQL Server

You have the column UserName and an alias UserName. It is the column value that is being used to sort the results, not the alias. While it is perfectly acceptable to use an alias name inside the ORDER BY clause, it cannot be used inside CASE WHEN statement.

The solution is to use a sub-query (or CTE):

DECLARE
    @Sortorder VARCHAR(5) = 'asc',
    @ColumnNumber INT = 9

SELECT * FROM (
    SELECT SUBSTRING(csu.UserName, /* removed for readability */) AS UserNameCopy, w.WorkItemId
    FROM [tasks].[WorkItems] w
    LEFT JOIN operations.CustomerServiceUser csu ON csu.UserId = w.AssignedToUserId
    WHERE w.[ShowInTaskList] = 1 AND UserName IS NOT NULL
) AS SubQuery
ORDER BY 
    CASE WHEN @ColumnNumber = 9 AND @SortOrder = 'asc'  THEN SubQuery.UserNameCopy END ASC,
    CASE WHEN @ColumnNumber = 9 AND @SortOrder = 'desc' THEN SubQuery.UserNameCopy END DESC

Alias not recognized in ORDER

Unfortunately aliases are not recognized in Access SQL. I used the ORDER BY (column number) which is elegant enough for me.

SELECT ORG, PR, NOUN, Elapsed, Target_Days, Supportable_Date,
      (Target_Days - Elapsed) AS Status
FROM qry_Gate_Status
ORDER BY ORG, Supportable_Date, 7;

Why does using '' or doesn't sort column aliases via ORDER BY in MySQL?

Single quotes (and, in MySQL, double quotes) stand for literal strings. So 'Full name' is just that: a literal string. Using that for sorting makes no sense, since the value is constant for all rows: as a result, the ordering of rows is undefined, meaning that the database is free to return rows in whatever order it likes.

Instead, use backticks, that are used for identifiers, so the order by refers to the expression aliases in the select clause.

Or better yet, use an alias that does not requires quoting, so you don’t have to worry about this all.

Can not order by alias in SQL Server 2012

Yes, you can use it in order by, but no in the case statement.

CREATE VIEW NAME
AS
SELECT BAUSER.USER_FNM + ' ' + BAUSER.USER_LNM AS USER_FULL_NAME --, next cols
FROM CAORAC CAORAC INNER JOIN CAACCO CAACCO
    ON CAORAC.ACCO_KEY = CAACCO.ACCO_KEY
    INNER JOIN BAUSER BAUSER
    ON CAORAC.USER_KEY = BAUSER.USER_KEY

SELECT              
    USER_FULL_NAME AS USER_FULL_NAME,
    CASE WHEN @cOrderBy = 'cUSER_FULL_NAME ASC' THEN USER_FULL_NAME END USER_FULL_NAME_ASC ,
    CASE WHEN @cOrderBy = 'cUSER_FULL_NAME DESC' THEN USER_FULL_NAME END USER_FULL_NAME_DESC
FROM NAME
ORDER 
    USER_FULL_NAME_ASC ASC,
    USER_FULL_NAME_DESC DESC

Or you can add case into your view too. It depends on puropse but it could be candidate for table value function. Or if you prefer more complex qry you can use derived table (instead of view).

Unable to ORDER BY on Column Alias when using CASE

Okay, here are some comments that I have.

1 - Please get away from old style joins. Use the INNER JOIN ON clause.

2 - There is no reason why an alias can not be used in the ORDER BY clause. Please see Itzik Ben-Gans posting on logical processing order. The SELECT arguments are processed way before the ORDER BY.

http://www.sql.co.il/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf

3 - Last but not least, a simple example (adventureworks) that make everyone with a hire date less than 2004 as Active, everyone else is in-active. This will sort by the status column.

Good luck.

John

-- Sample database
Use AdventureWorks2012
GO

-- Sample select showing alias works fine in a order by clause.
SELECT [LoginID] as login_id,
    CASE WHEN (e.HireDate < '20040101') THEN 'Active' 
    ELSE 'InActive' END AS emp_status
FROM [HumanResources].[Employee] as e
ORDER BY emp_status desc
GO

Since you changed your code above, here is a new answer to match. For a CASE statement on an ORDER BY you have to use the actual columns. For just a simple ORDER BY, the alias will work.

SO THE ANSWER is it ALL DEPENDS!!

Use AdventureWorks2012
GO

ALTER PROCEDURE usp_Sort_By_Column(@sort varchar(25))
AS
SELECT 
    [LoginID] as login_id,
    CASE WHEN (e.HireDate < '20040101') 
    THEN 'Active' ELSE 'InActive' END AS emp_status
FROM 
    [HumanResources].[Employee] as e
ORDER BY 
   (CASE 
       WHEN @sort = 'ID' THEN [LoginID] ELSE 
        (CASE WHEN (e.HireDate < '20040101') 
        THEN 'Active' ELSE 'InActive' END)
     END) 
GO

usp_Sort_By_Column 'STATUS'

Link to ORDER BY - Books On Line ...

http://msdn.microsoft.com/en-us/library/ms188385.aspx

Best answer for your crazy query: Make a sort column that is dynamic using the variable. Just order by the first column. Cleanest answer.

Use AdventureWorks2012
GO

ALTER PROCEDURE usp_Sort_By_Column(@sort varchar(25))
AS
SELECT 
    (CASE 
       WHEN @sort = 'ID' THEN [LoginID] ELSE 
        (CASE WHEN (e.HireDate < '20040101') 
        THEN 'Active' ELSE 'InActive' END)
     END) as Sort_Column,

    [LoginID] as login_id,
    CASE WHEN (e.HireDate < '20040101') 
    THEN 'Active' ELSE 'InActive' END AS emp_status
FROM 
    [HumanResources].[Employee] as e
ORDER BY 
   1
GO

usp_Sort_By_Column 'ID'

Can't use alias in mysql function as part of ORDER BY clause

The reason why that happens is because MySQL was processing ORDER BY in earlier versions (apparently including 5.0) is the following way:

1. If the entire ORDER BY clause is an integer, sort by the corresponding element of the projection clause (ORDER BY 2 sorts by the second element of the project clause)

2. If the entire ORDER BY clause is an identifier, search for a projection list element with such an alias, or, if none found, for a column with such a name, and sort by that.

3. Otherwise, parse the expression. Only column names, but not aliases, are visible during parsing.

So in your first query you get to the second case, hence you see the alias, in your second query you get to the third case, and hence do not see it.

UPDATE: Seems like PostgreSQL has exactly the same behavior. Look at answer by Peter Eisentraut here
[ ORDER BY Alias not working ]

He summarizes the same thing I said, but for PostgreSQL. Order By can have either an alias (output column), or an integer, or an expression referencing input columns.

---

Related Topics