Table Joined To Itself Appears Inefficient

Question

My ultimate goal is to compare two rows (only two will exist) and for a specific type and determine if its rows values are greater than the other rows type values. The result I came up with seems to be unwieldy which leaves me to wonder if it can be done in a faster or more elegant way.

---

I have created sql fiddle here which demonstrates the issue.

Say we are shipping cargo and our main route of type 1 needs to be compared to the other route type 2. Each route has a Wave value and a Roll value which need to be compared independently. Note that other route types (except main, it is always uses) can be compared to Main of type value 1, but only two at a time.

CREATE TABLE Shipping
(
[RouteID]         [INT]  NOT NULL,
[WaveValue]       [INT] NOT NULL, 
[RollValue]       [INT] NOT NULL

);

INSERT Shipping(RouteID, WaveValue, RollValue)
VALUES (1, 20, 2),  -- This is the main route
       (2, 10, 30); -- The other secondary route, (only 2 items ever in this table)

With the values currently in the table we expect that the MainsWave will be greater than the other's wave and the reverse for the Roll.

Here is the working sql, note that the true end result is only concerned with IsMainWaveGreater and IsMainRollGreater but all columns are shown for debug:

   SELECT Max1.RouteID
        , Max1.WaveValue
        , Max1.RollValue
        , MaxOther.RouteID
        , MaxOther.WaveValue
        , MaxOther.RollValue
  , IIF(Max1.RouteID = 1,
        IIF(Max1.WaveValue > MaxOther.WaveValue, 1, 0),
        IIF(Max1.WaveValue > MaxOther.WaveValue, 0, 1)) AS IsMainWaveGreater
  , IIF(Max1.RouteID = 1,
        IIF(Max1.RollValue > MaxOther.RollValue, 1, 0),
        IIF(Max1.RollValue > MaxOther.RollValue, 0, 1)) AS IsMainRollGreater
   FROM
    Shipping AS Max1
   INNER JOIN Shipping AS MaxOther ON Max1.RouteID > MaxOther.RouteID;

Since the row that could be read first and joined to the next may not be the Main route (remember always value 1 in RouteID for main), the sql has to have an IIF which checks on what type Max1 is and then swap the check/results accordingly.

Can this be optimized or even rewritten?

This is just one operation in a CTE process so other operations can be entertained.

Answer 1

With two rows per table any method will be good.

Since you are OK with hard-coding the value 1 for the main RouteID I'd do it like this:

SELECT
    CASE WHEN Main.WaveValue > Other.WaveValue THEN 1 ELSE 0 END AS IsMainWaveGreater
    ,CASE WHEN Main.RollValue > Other.RollValue THEN 1 ELSE 0 END AS IsMainRollGreater
FROM
    Shipping AS Main
    CROSS APPLY
    (
        SELECT
            Other.WaveValue
            ,Other.RollValue
        FROM Shipping AS Other
        WHERE Other.RouteID <> 1
    ) AS Other
WHERE Main.RouteID = 1
;

It assumes that there are only two rows in the table, if there are more "other" routes, then specify their specific RouteID in the CROSS APPLY instead of <> 1 to get only one row that's needed.

Here is SQL Fiddle.