How to place an ORDER BY clause in SQL between UNIONS

I want to implement simple SQL query that will return a sorted list. The problem is that I get syntax errors for placing the ORDER BY开发者_如何学Python clause anywhere I put it.

SELECT 
    fr.FunctionRoleID, fr.FunctionRoleInternalName
FROM 
    users u 
JOIN 
    UserRoles ur ON ur.UserID = u.UserID
JOIN 
    Roles_FunctionRoles rfr ON rfr.RoleID = ur.RoleID
JOIN 
    FunctionRoles fr ON fr.FunctionRoleID = rfr.FunctionRoleID 
WHERE 
    u.UserName = @UserName
    AND u.Active = 1

UNION 

SELECT 
    fr.FunctionRoleID, fr.FunctionRoleInternalName
FROM 
    Roles r
JOIN 
    Roles_FunctionRoles rfr ON rfr.RoleID = r.RoleID
JOIN 
    FunctionRoles fr ON fr.FunctionRoleID = rfr.FunctionRoleID
WHERE 
    r.RoleName = 'Authenticated Users'
    AND @UserName IS NOT NULL
    AND LEN(@UserName) > 0

What I want to insert:

ORDER BY fr.DisplayName ASC

EDIT

If I create a subquery using

SELECT * 
FROM 
     (
     [my initial query]
     )
ORDER BY 
    [COLUMN NAME] ASC

I get the following error message:

Incorrect syntax near 'ORDER'. Expected 'AS', 'ID' or 'QUOTED_id'

---

In most databases, you can only place an order by at the end of a union.

Because the union abstracts away individual table aliases, you only have to list the column name. So omit the fr. :

ORDER BY DisplayName

---

The ORDER BY clause needs to be placed after the last SELECT statement of the Union.

---

select * from(
*what you have there*
) as foo
order by DisplayName ASC

I'm not in front of an IDE so the syntax may be off a bit but that's the idea.

e: yeah, figured I'd jack up the syntax...alias added :)

---

Your not selecting DisplayName so you cannot use it to ORDER BY a set derived from a UNION. If you want to order by it and omit it from the results;

;WITH T (FunctionRoleID, FunctionRoleInternalName, DisplayName) AS (
    SELECT 
        fr.FunctionRoleID, fr.FunctionRoleInternalName, fr.DisplayName
    FROM users u
        JOIN UserRoles ur ON ur.UserID = u.UserID
        JOIN Roles_FunctionRoles rfr ON rfr.RoleID = ur.RoleID
        JOIN FunctionRoles fr ON fr.FunctionRoleID = rfr.FunctionRoleID 
    WHERE 
        u.UserName = @UserName
    AND 
        u.Active = 1

    UNION

    SELECT 
        fr.FunctionRoleID, fr.FunctionRoleInternalName, fr.DisplayName
    FROM 
        Roles r
        JOIN Roles_FunctionRoles rfr ON rfr.RoleID = r.RoleID
        JOIN FunctionRoles fr ON fr.FunctionRoleID = rfr.FunctionRoleID
    WHERE 
        r.RoleName = 'Authenticated Users'
        and @UserName is not null and LEN(@UserName) > 0
)
SELECT 
    FunctionRoleID, FunctionRoleInternalName
FROM T
    ORDER BY DisplayName

---

Try

SELECT * FROM (

SELECT  
    fr.FunctionRoleID, fr.FunctionRoleInternalName 



FROM  
    users u  
    JOIN UserRoles ur ON ur.UserID = u.UserID 
    JOIN Roles_FunctionRoles rfr ON rfr.RoleID = ur.RoleID 
    JOIN FunctionRoles fr ON fr.FunctionRoleID = rfr.FunctionRoleID  


WHERE  
    u.UserName = @UserName 
AND  
    u.Active = 1 


UNION  

    SELECT  
        fr.FunctionRoleID, fr.FunctionRoleInternalName 
    FROM  
        Roles r 
        JOIN Roles_FunctionRoles rfr ON rfr.RoleID = r.RoleID 
        JOIN FunctionRoles fr ON fr.FunctionRoleID = rfr.FunctionRoleID 
    WHERE  
        r.RoleName = 'Authenticated Users' 
        and @UserName is not null and LEN(@UserName) > 0 ) a

ORDER BY a.FunctionRoleInternalName ASC

Basically, you are selecting against the result of the UNION and then performing the ORDER BY...note that I use "FunctionRoleInternalName"...you can change that to "DiaplayName" only of you use that as a column ALIAS in the UNION queries...e.g. "FunctionRoleInternalName AS DisplayName"

---

For UNION, ORDER BY goes at the end and applies to the combined result of both queries; you can't order by a column that is not selected by both queries in the union.

what you need to do is select fr.DisplayName in both queries; then you can order by it.

If you don't want the display name to be one of the output columns, nest the whole thing in an outer query that retrieves just the columns you want.