Return row with most occurances

Databases
1

Ive got a table with many fields and rows. One field has a name of who will be working a file. Usually 1 of 10 different names will be in this field. I need to find which name appears the most and how many times it does.

2 
SELECT name
     , COUNT(*) AS times
  FROM daTable
GROUP
    BY name
ORDER
    BY times DESC

and then, depending on which database sytem you’re using (oracle? sybase? db2? postgresql?) you would alter that query slightly to return only the first row

:slight_smile:

3

Im having trouble with my order by… heres what i have…


SELECT Count(Table1.assigned) AS Expr1
FROM Table1
group by assigned
order by Expr1 DESC;

I actually dont need the value of who has the most occurances, just the amount of the most occurances. Im using access and its prompting me to specify the value of exp1?

Tried this as well form within my vba script:


SELECT count (assigned) as CountOfassigned FROM table1 GROUP BY assigned ORDER BY CountOfassigned DESC //gives me error in sql syntax
 
SELECT count (assigned) as CountOfassigned FROM table1 ORDER BY CountOfassigned DESC GROUP BY assigned // gives me too many too few paramters error
4 
SELECT MAX(humpty) AS most_occurrences
  FROM ( SELECT assigned
              , COUNT(*) AS humpty
           FROM Table1
         group 
             by assigned ) AS dumpty

:cool: