CODEWITHSUNDEEP
sqlsql-server-2005
kst
kst

Reputation: 23

selection of column based on max value

I have a table count with the attributes

I want to select a column having maximum value. How do I do that?

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

Upvotes: 2

Views: 234

Answers (3)

NirmalGeo
NirmalGeo

Reputation: 771

   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

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107816

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

Upvotes: 5

Steven Ryssaert
Steven Ryssaert

Reputation: 1967

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

Upvotes: 0

Related Questions