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\$
5
  • \$\begingroup\$ Is this code review question or stackoverflow? \$\endgroup\$ Nov 27, 2015 at 9:48
  • \$\begingroup\$ Meaning what exactly? \$\endgroup\$
    – kbd
    Nov 27, 2015 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\$ Nov 27, 2015 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\$
    – kbd
    Nov 27, 2015 at 9:56
  • \$\begingroup\$ Cool. Got it. I got lazy to observe that difference. Thanks. \$\endgroup\$ Nov 27, 2015 at 9:57

2 Answers 2

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\$
3
  • \$\begingroup\$ Good call, that works! \$\endgroup\$
    – kbd
    Nov 27, 2015 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\$
    – kbd
    Nov 27, 2015 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\$
    – kbd
    Nov 27, 2015 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\$
1
  • \$\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\$
    – kbd
    Dec 2, 2015 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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