CODEWITHSUNDEEP
sql-servert-sql
gnopfelbrie
gnopfelbrie

Reputation: 25

MAX of multiple columns with column name

I have following table in SQL Server:

CREATE TABLE table123
(
    ID INT PRIMARY KEY,
    Price1 INT,
    Price2 INT,
    Price3 INT,
    another_field int
)

INSERT  INTO table123
VALUES  (1, 4, 3, 2 , 112)
INSERT  INTO table123
VALUES  (2, 2, 3, 1 , 113)
INSERT  INTO table123
VALUES  (3, 5, 4, 6 , 114)
INSERT  INTO table123
VALUES  (4, 3, 4, 6 , 115)
GO

ID  Price1  Price2  Price3  another_field
 1   4       3       2       112
 2   2       3       1       113
 3   5       4       6       114
 4   3       4       6       115

What I want is the maximum value of the three prices but I want to know which column did hold the value. Like this table:

ID  Price   PriceLabel  another_field
 1   4        Price1     112
 2   3        Price2     113
 3   6        Price3     114
 4   6        Price3     115

I have seen multiple solutions to get the Max value for the Prices and adopted them:

WITH temp1 as (
SELECT ID, 
       ( CASE rn 
           WHEN 1 THEN Price1 
           WHEN 2 THEN Price2 
           ELSE Price3 
         END ) AS Price, 
       ( CASE rn 
           WHEN 1 THEN 'Price1' 
           WHEN 2 THEN 'Price2' 
           ELSE 'Price3' 
         END ) AS "PriceLabel" 
         ,another_field
FROM   table123 a 
       CROSS JOIN (SELECT 1 AS rn 
                   UNION 
                   SELECT 2 
                   UNION 
                   SELECT 3) b 
),
temp2 as (select ID, max(Price) AS Price from temp1 group by ID)

select t2.*,t1.PriceLabel,t1.another_field
from temp2 t2
join temp1 t1 on t1.ID = t2.ID AND t1.Price = t2.Price
GO

I feel there is a much smarter solution than this. Any help much appreciated.

Upvotes: 0

Views: 64

Answers (2)

AhmedHuq
AhmedHuq

Reputation: 469

Although an answr is already accepted, another way to address this is to use unpivot and rank to get the values. If, for some reason, you have more than three columns, then, you will have to just modify the unpivot statement to accommodate it.

declare @table123 table
(
    ID INT PRIMARY KEY,
    Price1 INT,
    Price2 INT,
    Price3 INT,
    another_field int
)

INSERT  INTO @table123
VALUES  (1, 4, 3, 2 , 112)
INSERT  INTO @table123
VALUES  (2, 2, 3, 1 , 113)
INSERT  INTO @table123
VALUES  (3, 5, 4, 6 , 114)
INSERT  INTO @table123
VALUES  (4, 3, 4, 6 , 115)

select * from @table123

-- UNPIVOT ID, another_field, price_number, price_value into CTE
;with upv as
(
    -- UNPIVOT ID, another_field, price_number, price_value into CTE
    select
    ID, another_field, price_number, price_value
    from
    @table123
    UNPIVOT
    (
        price_value
        for price_number in ([Price1], [Price2], [Price3])
    ) pvt
)

,RNK as
(
    -- Ranked table
    select *
    , ROW_NUMBER() over (partition by ID order by price_value desc) as RN 
    from upv
    )
select * from RNK where RN = 1

Returned result is:

enter image description here

Upvotes: 0

pakeha_by
pakeha_by

Reputation: 2244

Well, you can detect which price has max value manually (assuming that price can't be negative):

SELECT 
  ID, 
  CASE 
    WHEN Price1 IS NOT NULL AND Price1 >= ISNULL(Price2, 0) AND Price1 >= ISNULL(Price3, 0) THEN Price1
    WHEN Price2 IS NOT NULL AND Price2 >= ISNULL(Price3, 0) THEN Price2
    ELSE Price3
  END as Price,
  CASE 
    WHEN Price1 IS NOT NULL AND Price1 >= ISNULL(Price2, 0) AND Price1 >= ISNULL(Price3, 0) THEN 'Price1'
    WHEN Price2 IS NOT NULL AND Price2 >= ISNULL(Price3, 0) THEN 'Price2'
    ELSE 'Price3'
  END as PriceLabel,
  another_field
FROM table123

But it's better move prices to separate table (ID, Index, Price)

Upvotes: 2

Related Questions