Forum Stats

  • 3,838,627 Users
  • 2,262,385 Discussions
  • 7,900,712 Comments

Discussions

SQL Help, Pick highest Spend

Kodiak_Seattle
Kodiak_Seattle Member Posts: 565
edited Jan 14, 2010 5:26AM in SQL & PL/SQL
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 ?

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond
    Answer ✓
    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
    ;

Answers

This discussion has been closed.