SELECT ID_number, count( CU_number ) FROM MyTable GROUP BY ID_number order by 2 desc limit 10
will give you the top 10. Change 10 to whatever you want or take off the limit clause to get all records. On Fri, Oct 17, 2008 at 4:25 AM, dave aptiva <[EMAIL PROTECTED]>wrote: > Hello all, > > I'm new to sql and have a question if someone would be kind enough to help > me with, if I have a table that stores telemarketers by ID_number and the > customer that they spoke to by CU_number and I use a select statement such > as; > > > > SELECT ID_number, count( CU_number ) > > FROM MyTable > > GROUP BY ID_number; > > > > To find the number of calls that each telemarketer made, this works fine > but > how do I then use the results that are returned to find those telemarketers > that made the most telephone calls ? > > > > I tried SELECT ID_number, max( count( CU_number ) ) but this causes an > error > "#1111 - Invalid use of group function " > > > > I also tried > > > > SELECT ID_number, count( CU_number ) AS CC > > FROM MyTable > > GROUP BY ID_number > > WHERE CC = max( CC ); > > > > But this also causes an error, anyone able to offer some pointers am I able > to use sub queries ? > > > > Thanks Dave. > > > > > > > > -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
