How to get second highest value among multiple col

问题:

I have some data like below

    Name     Flag1   Flag2  Flag3  Flag4
    Jason    500     400    200    100
    Mark     400     299    250    183
    Tom      932     331    283    844

I was able to find the largest value among all 4 flags by writing

Max = CASE WHEN flag1>flag2 AND flag1>flag3 AND flag1>flag4 THEN 'flag1' 
           WHEN flag1<flag2 AND flag2>flag3 AND flag2>flag4 THEN 'flag2'
           WHEN flag1<flag3 AND flag2<flag3 AND flag3>flag4 THEN 'flag3'
           WHEN flag1<flag4 AND flag2<flag4 AND flag3<flag4 THEN 'flag4'END

Could you please help me with finding the second largest value within Flag1-Flag4? For example, the 2nd largest for Jason should be flag2

Thank you in advance!

回答1:

You can unpivot your data then make the query as below

SELECT name,flags,flag FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY name ORDER BY flag DESC) rn,*
    FROM
    (
        SELECT name, flag, flags
        FROM
        (
          SELECT 'json' name, 500 flag1,400  flag2,200 flag3, 100 flag4
          UNION ALL
          SELECT 'Mark' name, 400 flag1,299  flag2,250 flag3, 183 flag4
          UNION ALL
          SELECT 'Tom' name, 932 flag1,331  flag2,283 flag3, 844 flag4
        ) AS cp
        UNPIVOT 
        (
          flag FOR flags IN (flag1, flag2, flag3, flag4)
        ) AS up
    )x
)y
WHERE rn=2

p.s. In the middle of the query I just simulate your data with bunch of selects and union all

p,s Also you can get the first amount by this query too, look at rn = 2