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
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
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