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?

• Nikhil Vartak: Is this code review question or stackoverflow?
• kbd: Meaning what exactly?
• Nikhil Vartak: I mean should not this fit better to be asked on Stackoverflow instead of codereview site! I am just trying to understand.
• kbd: I'm not asking for help on achieving something, I'm looking for a better way to do what I already have. That's the definition of a code review, imho.
• Nikhil Vartak: Cool. Got it. I got lazy to observe that difference. Thanks.

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

• kbd: Good call, that works!
• kbd: I prefer the former of the two, looks cleaner, also (might be incorrect on this, but) think it's (fractionally) faster than the latter.
• kbd: I checked, if there's a clustered index the performance difference is theoretical (identical in human experience), for a non-clustered index Max beats order by limit (or top).

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 

• kbd: The where 1 = 1 is a cosmetic trick to prevent copy paste headaches. Granted, it's silly for small queries, but once you start using it, you'll regret it that one time you didn't (laziness is your friend).