Geek Answers Handbook

How to place an ORDER BY clause in SQL between UNIONS

I want to implement a simple SQL query that will return a sorted list. The problem is that I am getting syntax errors to place the ORDER BY anywhere.

 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

The following error message appears:

Invalid syntax next to "ORDER". Expected "AS", "ID", or "QUOTED_id"

+4
source share
6 answers

In most databases, you can only place order by at the end of the join.

Since the join abstracts the individual table aliases, you only need to specify the column name. Therefore, omit fr. :

 ORDER BY DisplayName
+2
source 
 select * from( *what you have there* ) as foo order by DisplayName ASC

I am not in front of the IDE, so the syntax may be a bit, but this is an idea.

e: yes, I thought I'd raise the syntax ... an alias was added :)

+2
source

The ORDER BY clause must be placed after the last Union SELECT statement.

+2
source

DisplayName not selected, so you cannot use it to ORDER BY set obtained from UNION . If you want to order it and omit 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
+2
source

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 select the result of UNION, and then execute ORDER BY ... note that I use "FunctionRoleInternalName" ... you can change this to "DiaplayName" only from what you use as the ALIAS column in UNION queries. .. for example, "FunctionRoleInternalName AS DisplayName"

+1
source

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

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

If you do not want the display name to be one of the output columns, paste all of this into an external query that retrieves only the columns you need.

0
source

More articles:


All Articles