SQL Help, Pick highest Spend

Kodiak_Seattle

Oralce 10G.

I have a Table with many unique records, kinda looks like this:

customer_key | BB | CL | LA
123 100 300 50
456 65 125 658
789 12 5 98

I need to take the highest spend.

customer_key | SPEND
123 300
456 658
789 98

Not sure how do this since, the field have different names ?

Frank Kulash

Hi,

Use a CASE expression.
Assuming the numbers are distinct and not NULL:

SELECT	customer_key
,	GREATEST ( bb
		 , cl
		 , la
		 )	AS spend
,	CASE
		WHEN  bb >= GREATEST (cl, la)	THEN  'BB'
		WHEN  cl >= la	     	  	THEN  'CL'
		      	    			ELSE  'LA'
	END		AS column_name
FROM	table_x
;

Frank Kulash

Hi,

Assuming the columns bb, cl and la are never NULL:

SELECT	customer_key
,	GREATEST ( bb
		 , cl
		 , la
		 )	AS spend
FROM	TABLE_X
;

Assuming the numbers can be NULL, is there a lower bound to their possible values (such as 0)?
If so, use NVL to map NULLs to an impossibly low value, e.g. NVL (bb, -1). You may want to use NULLIF to map that back to NULL in the event that all 3 columns are NULL.
If not, unpivot the 3 columns in to one column and use the aggregate MAX function.

Kodiak_Seattle

Cooool, let me try that out, thanks!

Kodiak_Seattle

Ok, this worked, I guess I need one more thing, I need an additional field that will tell me which column the Greatest Spend Came from, like BB, or CL, or LA ?

How would something like that be done ?

Frank Kulash

Hi,

Use a CASE expression.
Assuming the numbers are distinct and not NULL:

SELECT	customer_key
,	GREATEST ( bb
		 , cl
		 , la
		 )	AS spend
,	CASE
		WHEN  bb >= GREATEST (cl, la)	THEN  'BB'
		WHEN  cl >= la	     	  	THEN  'CL'
		      	    			ELSE  'LA'
	END		AS column_name
FROM	table_x
;

Kodiak_Seattle

Thank you for your time!

Aketi Jyuuzou

I like simple case expression

SELECT customer_key,GREATEST(bb,cl,la) AS spend
CASE GREATEST(bb,cl,la)
when bb then 'BB'
when cl then 'CL'
when la then 'LA' END AS column_name
FROM    table_x;