Find largest between 3 columns for each record

Question

Find largest between 3 columns for each record

Nunnery

Ten Centuries

Points: 1293
More actions
August 16, 2010 at 11:03 am

#72931

Hello,
I would like to have a SELECT query where I pull the max value out of 3 columns for each record. Would the following be the best way of achieving this or is there a more efficient way to do so. Also, if I use the method listed below should I place that in a UDF to help maintain readability?
SELECT ClientID
,CASE
WHEN ISNUMERIC(EEsEnrolledMedical) = 1 AND ISNUMERIC(EEsEnrolledDental) = 1 AND ISNUMERIC(EEsEnrolledVision) = 1 THEN
CASE
WHEN (EEsEnrolledMedical > EEsEnrolledDental) AND (EEsEnrolledMedical > EEsEnrolledVision) THEN EEsEnrolledMedical
WHEN (EEsEnrolledMedical > EEsEnrolledDental) AND (EEsEnrolledMedical = EEsEnrolledVision) THEN EEsEnrolledMedical
WHEN (EEsEnrolledMedical = EEsEnrolledDental) AND (EEsEnrolledMedical > EEsEnrolledVision) THEN EEsEnrolledMedical
WHEN (EEsEnrolledDental > EEsEnrolledMedical) AND (EEsEnrolledDental > EEsEnrolledVision) THEN EEsEnrolledDental
WHEN (EEsEnrolledDental > EEsEnrolledMedical) AND (EEsEnrolledDental = EEsEnrolledVision) THEN EEsEnrolledDental
WHEN (EEsEnrolledDental = EEsEnrolledMedical) AND (EEsEnrolledDental > EEsEnrolledVision) THEN EEsEnrolledDental
WHEN (EEsEnrolledVision > EEsEnrolledMedical) AND (EEsEnrolledVision > EEsEnrolledDental) THEN EEsEnrolledVision
WHEN (EEsEnrolledVision > EEsEnrolledMedical) AND (EEsEnrolledVision = EEsEnrolledDental) THEN EEsEnrolledVision
WHEN (EEsEnrolledVision = EEsEnrolledMedical) AND (EEsEnrolledVision > EEsEnrolledDental) THEN EEsEnrolledVision
END
WHEN ISNUMERIC(EEsEnrolledMedical) = 1 AND ISNUMERIC(EEsEnrolledDental) = 1 AND ISNUMERIC(EEsEnrolledVision) = 0 THEN
CASE
WHEN (EEsEnrolledMedical > EEsEnrolledDental) THEN EEsEnrolledMedical
WHEN (EEsEnrolledDental > EEsEnrolledMedical) THEN EEsEnrolledDental
WHEN (EEsEnrolledMedical = EEsEnrolledDental) THEN EEsEnrolledMedical
END
WHEN ISNUMERIC(EEsEnrolledMedical) = 1 AND ISNUMERIC(EEsEnrolledDental) = 0 AND ISNUMERIC(EEsEnrolledVision) = 1 THEN
CASE
WHEN (EEsEnrolledMedical > EEsEnrolledVision) THEN EEsEnrolledMedical
WHEN (EEsEnrolledVision > EEsEnrolledMedical) THEN EEsEnrolledVision
WHEN (EEsEnrolledMedical = EEsEnrolledVision) THEN EEsEnrolledMedical
END
WHEN ISNUMERIC(EEsEnrolledMedical) = 0 AND ISNUMERIC(EEsEnrolledDental) = 1 AND ISNUMERIC(EEsEnrolledVision) = 1 THEN
CASE
WHEN (EEsEnrolledDental > EEsEnrolledVision) THEN EEsEnrolledDental
WHEN (EEsEnrolledVision > EEsEnrolledDental) THEN EEsEnrolledVision
WHEN (EEsEnrolledDental = EEsEnrolledVision) THEN EEsEnrolledDental
END
WHEN ISNUMERIC(EEsEnrolledMedical) = 1 AND ISNUMERIC(EEsEnrolledDental) = 0 AND ISNUMERIC(EEsEnrolledVision) = 0 THEN EEsEnrolledMedical
WHEN ISNUMERIC(EEsEnrolledMedical) = 0 AND ISNUMERIC(EEsEnrolledDental) = 1 AND ISNUMERIC(EEsEnrolledVision) = 0 THEN EEsEnrolledDental
WHEN ISNUMERIC(EEsEnrolledMedical) = 0 AND ISNUMERIC(EEsEnrolledDental) = 0 AND ISNUMERIC(EEsEnrolledVision) = 1 THEN EEsEnrolledVision
ELSE 0
END AS 'MaxEnrolled'
FROM tAudit

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

i think its better if you create function which take three variable or whatever number of column you have and then pass the column names on those variable.

then within function you can code to compare the values. and run the select query using the function... something like

select column1,column2,column3, dbo.functionname(column1,column2,column3) as maxvalue from yourtable.

----------
Ashish

LutzM SSC Guru Points: 107049 More actions · Answer 2

I would use UNPIVOT (EDIT: or UNION ALL if the system is before SS2K5) to normalize the data and a MAX() aggregation grouped by ClientID.

For a coded version I'd like to have some data to play with in a ready to use format as described in the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

MIN/MAX Across Multiple Columns

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906

This script demonstrates two methods for finding the maximum value in a row across a set of columns in the row when any or all of the columns are allowed to be null or equal.

Method 1 uses a UNION ALL sub query for all the columns with a MAX. It is much simpler to code and test, especially when you get much past 4 columns. Adding another column is as simple as adding one more SELECT to the subquery.

Method 2 uses a CASE statement to determine the MAX. It is much more complex to code (and test), and gets exponentially harder to code as the number of columns goes up. I think 5 or 6 columns may be about the limit of complexity of coding that you would want to take on. One advantage of this script is that you can use the simpler to code Method 1 to test the more complex code for the Method 2 if you choose to implement it as a CASE statement.