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.

• Malachi: what happens if all the values are the same?
• BenVlodgi: then it doesn't matter, Max, Mid, Min will just have those same values
• Malachi: 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. setting variables over and over again is not very efficient in a RDBMS.
• Vogel612: @Malachi was thinking the exact same.. insert the variables into a temp-table and select ordered..

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

• Malachi: I assumed that this was a stored procedure and that @lLength and the others were parameters?
• Vogel612: I think dimension column is totally overkill here. in fact it'd be alright to just have a small id column. The dimension is unused information and just clutters your structure.
• Malachi: @Vogel612, I assumed that they wanted to know which dimension was the largest and didn't think that having extra code to decode an ID was necessary when it can be written into the initial code and just selected. but then again they are only grabbing the dimension size, because really any of the 3 dimensions can be the width depending on how you look at it.
• BenVlodgi: @Malachi the length, width and height variables come from parameters, yes.
• BenVlodgi: @Malachi I do actually need the variables in the end for @lMax...

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;