selection of column based on max value

I have a table count with the attributes

• count1
• count2
• count3
• count4
• count5

I want to select a column having maximum va开发者_如何学编程lue. How do I do that?

The count represents page hits and I want to select a column having maximum hits and display it.

You can use a doubly nested subquery

select 
  (select max(count1)
   from (
    select count1 union all
    select count2 union all
    select count3 union all
    select count4 union all
    select count5) X) as MaxCount
from tbl

There are a few ways, involving PIVOT or a Temp Table, but i think the easiest to understand is using Case for this.

SELECT
    CASE
        WHEN count1 >= count2 AND count2 >= count3 AND count1 >= count4 AND count1 >= count5 THEN count1
        WHEN count2 >= count3 AND count2 >= count4 AND count2 >= count5 THEN count2
        WHEN count3 >= count4 AND count3 >= count5 THEN count3
        WHEN count4 >= count5 THEN count4
        ELSE count5
    END AS highestCount

   select MAX(max_count) FROM
   (
   select  count1 as max_count from count 
   UNION
   select  count2 as max_count from count 
   UNION
   select  count3 as max_count from count 
   UNION
   select  count4 as max_count from count 
   UNION 
   select  count4 as max_count from count
   )

I would not suggest using Select as it takes lot of time to generate the same. Triggers would be better