Sql Server: Getting the highest value based on two uniqueidentifiers - possible to avoid union?

Question

I have two riscProfileTypeIds (both of type uniqueidentifier) of which I need the one with the highest sequence number (the highest of the two risc profiles, at business level). The below query does the job, but I'm not very happy with the union, I wonder if there's a way to do the same without it.

SELECT [ID]
FROM [RiscProfileType]
WHERE 1 = 1
AND [Active] = 1
AND [Sequence] = (
    SELECT MAX([Sequence])
    FROM (
        SELECT [Sequence]
        FROM [RiscProfileType]
        WHERE 1 = 1
        AND [Active] = 1
        AND [ID] = @riscProfileTypeId1
        UNION
        SELECT [Sequence]
        FROM [RiscProfileType]
        WHERE 1 = 1
        AND [Active] = 1
        AND [ID] = @riscProfileTypeId2
    ) src
)

The [RiscProfileType] table is very small (4 records), so performance isn't much of an issue but still I prefer to write the best query possible.

Put simply it contains:

[Risc]          [Sequence]
Unacceptable    4
High            3
Normal          2
Low             1

I don't think a join is possible, because there's no real link between the entities.

Any suggestions on improving this query, or is this fine?

Answer 1

Why don't you use IN instead of the JOIN?

SELECT MAX([Sequence])
FROM [RiscProfileType]
WHERE 1 = 1
   AND [Active] = 1
   AND [ID] IN (@riscProfileTypeId1, @riscProfileTypeId2)

Or rethink the logic:

SELECT [ID]
FROM [RiscProfileType]
WHERE 1 = 1
   AND [Active] = 1
   AND [ID] IN (@riscProfileTypeId1, @riscProfileTypeId2)
ORDER BY [Sequence] DESC 
LIMIT 1

Answer 2

The above answer is correct but you can also use top instead of limit if you are using SQL Server for this case. we do not need 1=1 in where clause. Query will look like:

SELECT TOP 1 [ID]
FROM [RiscProfileType]
WHERE [Active] = 1
   AND [ID] IN (@riscProfileTypeId1, @riscProfileTypeId2)
ORDER BY [Sequence] DESC