Command that selects Max Value from multiple columns across a row in SQL Server

View previous topic View next topic Go down

Command that selects Max Value from multiple columns across a row in SQL Server

Post  Jeff Smith on Wed Dec 05, 2012 10:43 am

I am looking for a function in SLQ Server that essentially acts like COALESCE but instead of selecting the first value that isn't NULL, it selects the maximum value.

I have a process that looks are 4 fields. For each row, I need to pull the maximum value from the 4 fields. Sort of like MAX(Col1, Col2, Col3, Col4), but the MAX function won't do this.



Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Command that selects Max Value from multiple columns across a row in SQL Server

Post  TheNJDevil on Wed Dec 05, 2012 12:31 pm

Syntax probably not correct, but is this what you are looking for? it would return the largest value from the 4 passed in.

Declare @MX int
SET @MX = -9999
If(@Num1 > @MX) SET @MX = @Num1

If(@Num2 > @MX) SET @MX = @Num2

If(@Num3 > @MX) SET @MX = @Num3

If(@Num4 > @MX) SET @MX = @Num4

Return @MX

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

use CASE to test multiple conditions

Post  benlotter on Fri Dec 27, 2013 2:16 pm

Very old post but in case someone else stumbles on this, here's a possible way to do this. If none of your values allow for NULL then you can simplify by removing the ISNULL and the ELSE of the case be ELSE Amount4. Beyond that this example works in SQL Server 2008 and above.

Code:
DECLARE @Purchases TABLE (
   ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   Amount1 money NOT NULL,
   Amount2 money NULL,
   Amount3 money NULL,
   Amount4 money NULL
)
INSERT INTO @Purchases (Amount1,Amount2,Amount3,Amount4)
VALUES
   (10,9,8,NULL),
   (1,10,NULL,4),
   (1,2,10,3),
   (7,8,9,10)

SELECT *,
Max_Amount =
CASE
   WHEN (Amount1 >= ISNULL(Amount2,0) AND Amount1 >= ISNULL(Amount3,0) AND Amount1 >= ISNULL(Amount4,0)) THEN Amount1
   WHEN (Amount2 >= Amount1 AND Amount2 >= ISNULL(Amount3,0) AND Amount2 >= ISNULL(Amount4,0)) THEN Amount2
   WHEN (Amount3 >= Amount1 AND Amount3 >= ISNULL(Amount2,0) AND Amount3 >= ISNULL(Amount4,0)) THEN Amount3
   WHEN (Amount4 >= Amount1 AND Amount4 >= ISNULL(Amount2,0) AND Amount4 >= ISNULL(Amount3,0)) THEN Amount4
   ELSE NULL --if all 4 values are NULL
END

FROM @Purchases
GO
avatar
benlotter

Posts : 2
Join date : 2011-08-12
Age : 40
Location : Wisconsin

View user profile http://benjaminlotter.com/

Back to top Go down

Re: Command that selects Max Value from multiple columns across a row in SQL Server

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum