Sorting three Decimal values

Question

I have been given three values: @lLength, @lWidth, @lHeight. I need to take these DECIMAL(4,2) values and set the values of @lMax, @lMid, @lMin with the values I've been given in order of value. So whichever value of Length, Width or Height is the greatest will be stored in Max, and the second created in Mid, and the lowest value in Min.

Note: I am limited to Microsoft SQL Server 2008.

-- Declarations are for testing purposes only
DECLARE @lLength DECIMAL(4,2) = CAST('1.0' AS DECIMAL(4,2))
DECLARE @lWidth  DECIMAL(4,2) = CAST('2.0' AS DECIMAL(4,2))
DECLARE @lHeight DECIMAL(4,2) = CAST('3.0' AS DECIMAL(4,2))
DECLARE @lMax    DECIMAL(4,2)
DECLARE @lMid    DECIMAL(4,2)
DECLARE @lMin    DECIMAL(4,2)

--Determine which is higher and store it.
IF @lLength > @lWidth BEGIN
    SET @lMax = @lLength
    SET @lMid = @lWidth
END ELSE BEGIN
    SET @lMax = @lWidth
    SET @lMid = @lLength
END

--Now determine where the 3rd value needs to be inserted
IF @lHeight < @lMid BEGIN
    SET @lMin = @lHeight
END ELSE IF @lHeight < @lMax BEGIN
    SET @lMin = @lMid
    SET @lMid = @lHeight
END ELSE BEGIN
    SET @lMin = @lMid
    SET @lMid = @lMax
    SET @lMax = @lHeight
END

--This select is for testing purposes only
SELECT @lMax as MAX, @lMid AS MID, @lMin AS MIN

The code above works, and I added declarations and a select to make that readily apparent. I am looking for a general review of this operation, perhaps a better way to do this. You can criticize the naming convention, but that is something I have no control over. Also, I do not have to worry about scalability of this operation unless our world gets a 4th physical dimension in the near future.

Answer 1

You can use the variables directly in a table value constructor.

SELECT @lMin = CASE WHEN RN = 1 THEN Size ELSE @lMin END,
       @lMid = CASE WHEN RN = 2 THEN Size ELSE @lMid END,
       @lMax = CASE WHEN RN = 3 THEN Size ELSE @lMax END
FROM   (SELECT Size,
               ROW_NUMBER() OVER (ORDER BY Size) AS RN
        FROM   (VALUES (@lLength),
                       (@lWidth),
                       (@lHeight)) V(Size)) T 

SQL Fiddle

Answer 2

I don't know how you are using this information, but I think that you could create a very small table variable or temp table with like one column and then sort and select the information in order.

DECLARE @Table1 TABLE
(
     Dimension VARCHAR(10),
     Size DECIMAL(4,2)
)

INSERT INTO @Table1
(
     Dimension
     , Size
)
VALUES
('Length', @lLength),('Width', @lWidth),('Height',@lHeight)


SELECT * FROM @Table1 ORDER BY Size DESC

This eliminates 3 variables and all if statements (which are not very efficient in RDBMS's) Setting variables over and over again is not very efficient in a RDBMS.

you can select these into the variables like this

SET @lMax = SELECT TOP (1) FROM @Table1 ORDER BY Size DESC
WITH orderedTable AS
(
    SELECT Size, ROW_NUMBER() OVER (ORDER BY Size) as `RowNumber`
    FROM @Table1
)
SET @lMid = SELECT Size FROM orderedTable WHERE RowNumber = 2; 

WITH orderedTable AS
(
    SELECT Size, ROW_NUMBER() OVER (ORDER BY Size) as `RowNumber`
    FROM @Table1
)
SET @lMin = SELECT Size FROM orderedTable WHERE RowNumber = 3;

I am sure that Phrancis could come up with a better query than this, especially because I call the same CTE twice and I know that it could be more efficient perhaps with a temp table or something.

---

after seeing what @Phrancis is going to post I came up with something to get rid of the second CTE

because there are only 3 numbers you can subtract the middle number and the largest number to retrieve the minimum number using mathematics, like this

SET @lMax = SELECT TOP (1) FROM @Table1 ORDER BY Size DESC
WITH orderedTable AS
(
    SELECT Size, ROW_NUMBER() OVER (ORDER BY Size) as `RowNumber`
    FROM @Table1
)
SET @lMid = SELECT Size FROM orderedTable WHERE RowNumber = 2; 
SET @lMin = SELECT (SUM(Size) - @lMid - @lMax) FROM @Table1 --Thanks @Phrancis

So the Whole thing looks like this

DECLARE @Table1 TABLE
(
     Dimension VARCHAR(10),
     Size DECIMAL(4,2)
)

INSERT INTO @Table1
(
     Dimension
     , Size
)
VALUES
('Length', @lLength),('Width', @lWidth),('Height',@lHeight)

SET @lMax = SELECT TOP (1) FROM @Table1 ORDER BY Size DESC
WITH orderedTable AS
(
    SELECT Size, ROW_NUMBER() OVER (ORDER BY Size) as `RowNumber`
    FROM @Table1
)
SET @lMid = SELECT Size FROM orderedTable WHERE RowNumber = 2; 
SET @lMin = SELECT (SUM(Size) - @lMid - @lMax) FROM @Table1 --Thanks @Phrancis

I removed SELECT * FROM @Table1 ORDER BY Size DESC because we are filling in the variables in a different way.

---

If you were using this information in a program or something then I would tell you to just do it there, but it sounds like you are stuck in SQL Server

Answer 3

The CAST() on these variables seems redundant:

DECLARE @lLength DECIMAL(4,2) = CAST('1.0' AS DECIMAL(4,2))
DECLARE @lWidth  DECIMAL(4,2) = CAST('2.0' AS DECIMAL(4,2))
DECLARE @lHeight DECIMAL(4,2) = CAST('3.0' AS DECIMAL(4,2))

Why not just DECLARE @lLength DECIMAL(4,2) = 1.0 (etc.)?

@Malachi's CTE solution seems like it would work good, but I'm going to propose using a temp table as well. I recommend to test both to see which has best performance. I do find the whole operation to be a bit odd, but I'm sure in context it makes sense.

-- Declarations are for testing purposes only
DECLARE @lLength DECIMAL(4,2) = 1.0
DECLARE @lWidth  DECIMAL(4,2) = 2.0
DECLARE @lHeight DECIMAL(4,2) = 3.0
-- Put dimensions into a temp table
CREATE TABLE #Dimensions
    (
    value DECIMAL(4,2)
    );
INSERT INTO #Dimensions(value)
VALUES
    (@lLength),
    (@lWidth),
    (@lHeight);
-- Find Max, Min and Mid using simple calculations
DECLARE @lMax    DECIMAL(4,2) = (SELECT MAX(value) FROM #Dimensions);
DECLARE @lMin    DECIMAL(4,2) = (SELECT MIN(value) FROM #Dimensions);
DECLARE @lMid    DECIMAL(4,2) = (SELECT SUM(value) - @lMin - @lMax);
-- Get rid of temp table
DROP TABLE #Dimensions;