2
\$\begingroup\$

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?

\$\endgroup\$
  • \$\begingroup\$ Is this code review question or stackoverflow? \$\endgroup\$ – Nikhil Vartak Nov 27 '15 at 9:48
  • \$\begingroup\$ Meaning what exactly? \$\endgroup\$ – Spikee Nov 27 '15 at 9:50
  • \$\begingroup\$ I mean should not this fit better to be asked on Stackoverflow instead of codereview site! I am just trying to understand. \$\endgroup\$ – Nikhil Vartak Nov 27 '15 at 9:54
  • \$\begingroup\$ 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. \$\endgroup\$ – Spikee Nov 27 '15 at 9:56
  • \$\begingroup\$ Cool. Got it. I got lazy to observe that difference. Thanks. \$\endgroup\$ – Nikhil Vartak Nov 27 '15 at 9:57
2
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ Good call, that works! \$\endgroup\$ – Spikee Nov 27 '15 at 9:48
  • \$\begingroup\$ I prefer the former of the two, looks cleaner, also (might be incorrect on this, but) think it's (fractionally) faster than the latter. \$\endgroup\$ – Spikee Nov 27 '15 at 10:04
  • \$\begingroup\$ 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). \$\endgroup\$ – Spikee Nov 27 '15 at 10:16
1
\$\begingroup\$

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 
\$\endgroup\$
  • \$\begingroup\$ 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). \$\endgroup\$ – Spikee Dec 2 '15 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.