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.
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 aType
column as well - how to we know which route is the "main" and which is the "other"?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.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.