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 )
[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?