SQL Server: Is it possible to UNION two tables in a SELECT statement without using a temp table?

Question

I am creating a stored procedure that sets 3 variables based on queries and I have to return the MAX value of them. I am trying to do so without using a temp table however the following gives me the error Incorrect styntax near ')'

SELECT MAX(PermissionID)
FROM (SELECT @ContactPermission As PermissionID UNION ALL
      SELECT @GroupPermission As PermissionID)

I have also tried

SELECT MAX((SELECT @ContactPermission UNION ALL SELECT @GroupPermission))

and I get the error Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I have also tried bypassing the variables and using a UNION in a subquery...

SELECT MAX(PermissionID)
FROM (SELECT PermissionID
        FROM PermissionContact
        WHERE ContactID = @ContactID
      UNION ALL
      SELECT PermissionID
      FROM PermissionGroup
      WHERE GroupID = @GroupID)

This also gives the Incorrect syntax near ')'

Any thoughts or suggestions? Are temp tables my only option?

score 3 · Accepted Answer · answered Sep 02 '11 at 23:08

3

You need to give your derived table an alias.

SELECT MAX(PermissionID)
FROM (SELECT @ContactPermission As PermissionID UNION ALL
      SELECT @GroupPermission As PermissionID) as T

answered Sep 02 '11 at 23:08

Mikael Eriksson

136,425
22
210
281

So simple and quick... Thanks a ton, I will mark as answer in a few min when SO lets me. – jon3laze Sep 02 '11 at 23:10
3

+1 Can also use `SELECT MAX(PermissionID) FROM (VALUES(@ContactPermission, @GroupPermission)) T(PermissionID)` – Martin Smith Sep 02 '11 at 23:11

score 1 · Answer 2 · answered Sep 06 '11 at 13:47

Or just use a case statement

Select Case When @ContactPermission > @GroupPermission 
       Then @ContactPermission Else @GroupPermission End PermissionID

or,

SELECT Case When c.PermissionID > g.PermissionId
        Then c.PermissionID Else g.PermissionId End
FROM PermissionContact c Cross Join PermissionGroup g   
Where c.ContactID = @ContactID
    And g.GroupID = @GroupID

SQL Server: Is it possible to UNION two tables in a SELECT statement without using a temp table?

2 Answers2