SQL Help, Pick highest Spend

Kodiak_Seattle · Jan 12, 2010 6:42PM

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 · Jan 12, 2010 7:44PM

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 · Jan 12, 2010 6:48PM

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 · Jan 12, 2010 6:51PM

Cooool, let me try that out, thanks!

Kodiak_Seattle · Jan 12, 2010 7:23PM

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 · Jan 12, 2010 7:44PM

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 · Jan 13, 2010 12:17PM

Thank you for your time!

Aketi Jyuuzou · Jan 14, 2010 5:26AM

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;

Forum Stats

Quick Links

Categories

SQL Help, Pick highest Spend

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

SQL Help, Pick highest Spend

Best Answer

Answers