Finding the most occuring number 1

[CzechNET]

I have a table that looks like this

tbl_name
-------
ID
fldCATE
fldSOME

and I need to find out what's the most occurring fldCATE value (it's an integer btw)no matter the rest, so say if value 5 is there 60 times while value 1 is there only 25 times, the value 5 should be returned. Any ideas ?

 

[r937]

untested:

select fldCATE
  from tbl_name
group by fldCATE
having count(*)
     = ( select max(occurrences)
           from (
         select fldCATE
              , count(*) as occurrences
           from tbl_name
         group by fldCATE
                ) as counts 
       )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[CzechNET]

Thanks