?
Solved

select the frequency member ID in MYSQL

Posted on 2008-01-31
4
Medium Priority
?
225 Views
Last Modified: 2010-03-20
How can I select maximim top frequency duplicate MembeID
CREATE TABLE `insurance` (
  `insurprimarykey` int(11) NOT NULL AUTO_INCREMENT,
  `Insurkey` int(11) DEFAULT '0',
  `MembeID` int(11) DEFAULT NULL,
  `MemberName` varchar(250) DEFAULT NULL,
  `Amount` decimal(11,0) DEFAULT NULL,  
  PRIMARY KEY  (`insurprimarykey`),
  KEY `Membe ID` (`MembeID`)
) ENGINE=InnoDB AUTO_INCREMENT=401 DEFAULT CHARSET=tis620;

Open in new window

0
Comment
Question by:teera
  • 2
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 800 total points
ID: 20786704
SELECT `MembeID`, COUNT(*) cnt FROM `insurance`
GROUP BY `MembeID` ORDER BY cnt DESC LIMIT 1
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 20786731
try this
select top 1 * from(
select MembeID ,count(*) as c from insurance
group by MembeID) as t
order by c desc

Open in new window

0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20786748
@HuyBD: Just for my learning, does TOP 1 work in MySQL as well ?
0
 
LVL 17

Assisted Solution

by:HuyBD
HuyBD earned 400 total points
ID: 20786764
sorry,
select * from(
select MembeID ,count(*) as c from insurance
group by MembeID) as t
order by c desc
limit 1

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question