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.

[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
    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.

  • 1
    Roughly how many rows are typically in the Shipping table? Do you want to compare every row to all the other rows of the table? I.e. if there are 100 routes, do you want to compare every route to the other 99 routes? Also, when you say "main route of type 1" and "other route type 2", is there a Type column as well - how to we know which route is the "main" and which is the "other"? Feb 17, 2016 at 21:56
  • @DanielHutmacher The Shipping table should only have 2 rows. In real life it is a target table created on a CTE business logic born out of other CTE's. Main is always 'RouteId' of value 1. I will update the post.
    – ΩmegaMan
    Feb 18, 2016 at 1:56
  • nested ctes are an optimization disaster, but i guess they make your code more legible. also, what indexes do you have on this table? and i cant see sqlfiddle, could you please post the repro on this site? thanks!
    – swasheck
    Feb 18, 2016 at 3:05
  • @swasheck full repro schema spec is above in the example. You should be able to change it into a temp table on your local box and run with the example above. Or am I missing something?
    – ΩmegaMan
    Feb 18, 2016 at 3:14
  • 1
    @OmegaMan sorry, i couldnt tell what was going on with sqlfiddle so i didnt know if it was the same. so anyway, you mentioned CTEs. are these CTEs the equivalent of this Shipping table? if so, could you post the ddl for the base tables and a rough estimate of the row counts? also, having the CTE definitions would be nice as well. a self-joind CTE has a strong possibility of being evaluated twice since there isnt any guarantee of intermediate materialization with them.
    – swasheck
    Feb 18, 2016 at 3:17

1 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:

    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
    Shipping AS Main
        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.

Your Answer

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

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