Select the Greater of 3 variables and return a different variable SQL 2014

p-plater
p-plater used Ask the Experts™
on
I am Using SQL Server 2014
I have a stored procedure that has 6 parameters.

@OrderID1
@OrderID2
@OrderID3
@OrderValue1
@OrderValue2
@OrderValue3

I need to find the Greatest Order Value and Return the OrderID.

I can select the Max Order Value but I need the OrderID of the Highest Order Value

SELECT MAX(v) FROM (VALUES (@OrderValue1), (@OrderValue2), (@OrderValue3)) AS value(v)


Many Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
you no need to have a complex logic, just do the value comparison will do?

try:

CREATE PROCEDURE yourSP
@OrderID1 Int,
@OrderID2 Int,
@OrderID3 Int,
@OrderValue1 Int,
@OrderValue2 Int,
@OrderValue3 Int
AS  
    IF @OrderValue1 > @OrderValue2
        IF @OrderValue1 > @OrderValue3
            SELECT @OrderID1
        ELSE    
            SELECT @OrderID3
    ELSE
        IF @OrderValue2 > @OrderValue3
            SELECT @OrderID2
        ELSE    
            SELECT @OrderID3
    
RETURN
GO

Open in new window

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial