Select a number of records that certain field appears most often?

[raulgh]

Dear all:

I am newbie to SQL and I encountered a 'difficult' problem in development.Right now I have table that records all the SMS sent out by the customers. The table fields are defined as bellow:

indexID int(11) //primary key (auto increment)
sender varchar(50) //sender's nick name
datetime datetime //the time when sender sends out sms
mobile varchar(20) //sender's mobile number
messages varchar(200) //the message sent
status(100) //user status

I need to find out the 5 most active usrs from this table. So is to find the users whose name appeared most often in the table and pass back their names. We can use MAX(Count) to easily get the most often one, but how to get the most 5? Please give me some highlights on this. Thanks a lot

 

[Jamfool]

SET ROWCOUNT 5
select sender,count(sender) from table_name
group by sender
order by count(sender) desc

 

[Jamfool]

or u could use TOP

select TOP 5 tname,count(tname) from p4w
group by tname
order by count(tname) desc

 

[raulgh]

Hi JamFool:

Thanks for reply. However, when I try your SQL statement, I get error msg saying "invalid use of group function".My data engine is MySQL. Is it allowed to use order by count(*)? Pls clear this doubts for me.

 

[Jamfool]

try this instead

select TOP 5 tname,count(*) as counter from p4w
group by tname
order by counter desc

 

[swampBoogie]

TOP and ROWCOUNT are specific to SQL server and does not work in Mysql.

select tname,count(*) as counter from p4w
group by tname
order by counter desc
limit 5

The above code is specific to Mysql and not standard.

 

[raulgh]

Thanks a lot Jamfool and Swampboogie. Both of your codes works!