Find greatest value in a series of columns and select a related column value

Question

Find greatest value in a series of columns and select a related column value

mcinvalek

SSC-Addicted

Points: 429
More actions
October 10, 2014 at 1:04 pm

#311799

Table Structure
Camp1_ID, Camp1_Amt, Camp1_PCT, Camp2_Id, Camp2_Amt, Camp2_PCT....,Camp20_ID, Camp_20_Amt, Camp20_Pct
I need to search through the 'Camp_PCT' columns and find the max value, then select the cooresponding Camp_ID. If, the Camp_PCT values are blank, then find the largest Camp_Amt value and return the cooresponding Camp_ID number, For example, If Camp3_PCT is the largest in record 1, then I need to return Camp3_ID.
My only thought is that this may be possible with a pivot table.
Any suggestions would be greatly appreciated.

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

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

mcinvalek (10/10/2014)
Table Structure
Camp1_ID, Camp1_Amt, Camp1_PCT, Camp2_Id, Camp2_Amt, Camp2_PCT....,Camp20_ID, Camp_20_Amt, Camp20_Pct
I need to search through the 'Camp_PCT' columns and find the max value, then select the cooresponding Camp_ID. If, the Camp_PCT values are blank, then find the largest Camp_Amt value and return the cooresponding Camp_ID number, For example, If Camp3_PCT is the largest in record 1, then I need to return Camp3_ID.
My only thought is that this may be possible with a pivot table.
Any suggestions would be greatly appreciated.

Unpivot the data based on the PK and do a simple MAX/GROUP BY the PK. Either the UNPIVOT or CROSS APPLY method will work.

I'd also recomend normalizing that table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

Just for fun, here is a union alternative, certain that this is not the most efficient solution but it is simple to write and fun to play with, uses XML wildcard instead of a UNION. Most of the code is for setting up the sample data, only the last few lines are the actual query.

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF EXISTS (SELECT OBJECT_ID(N'dbo.TBL_SAMPLE_DATA')) DROP TABLE dbo.TBL_SAMPLE_DATA;

CREATE TABLE dbo.TBL_SAMPLE_DATA

(

Camp01_ID INT NOT NULL

,Camp01_PCT MONEY NOT NULL

,Camp01_Amt MONEY NOT NULL

,Camp02_ID INT NOT NULL

,Camp02_PCT MONEY NOT NULL

,Camp02_Amt MONEY NOT NULL

,Camp03_ID INT NOT NULL

,Camp03_PCT MONEY NOT NULL

,Camp03_Amt MONEY NOT NULL

,Camp04_ID INT NOT NULL

,Camp04_PCT MONEY NOT NULL

,Camp04_Amt MONEY NOT NULL

,Camp05_ID INT NOT NULL

,Camp05_PCT MONEY NOT NULL

,Camp05_Amt MONEY NOT NULL

,Camp06_ID INT NOT NULL

,Camp06_PCT MONEY NOT NULL

,Camp06_Amt MONEY NOT NULL

,Camp07_ID INT NOT NULL

,Camp07_PCT MONEY NOT NULL

,Camp07_Amt MONEY NOT NULL

,Camp08_ID INT NOT NULL

,Camp08_PCT MONEY NOT NULL

,Camp08_Amt MONEY NOT NULL

,Camp09_ID INT NOT NULL

,Camp09_PCT MONEY NOT NULL

,Camp09_Amt MONEY NOT NULL

,Camp10_ID INT NOT NULL

,Camp10_PCT MONEY NOT NULL

,Camp10_Amt MONEY NOT NULL

);

DECLARE @SAMPLE_SIZE INT = 1000;

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

INSERT INTO dbo.TBL_SAMPLE_DATA

(

Camp01_ID

,Camp01_PCT

,Camp01_Amt

,Camp02_ID

,Camp02_PCT

,Camp02_Amt

,Camp03_ID

,Camp03_PCT

,Camp03_Amt

,Camp04_ID

,Camp04_PCT

,Camp04_Amt

,Camp05_ID

,Camp05_PCT

,Camp05_Amt

,Camp06_ID

,Camp06_PCT

,Camp06_Amt

,Camp07_ID

,Camp07_PCT

,Camp07_Amt

,Camp08_ID

,Camp08_PCT

,Camp08_Amt

,Camp09_ID

,Camp09_PCT

,Camp09_Amt

,Camp10_ID

,Camp10_PCT

,Camp10_Amt

)

SELECT

NM.N