I am looking for a mysql query to search and list most common pairs with their IDs and number of occurence. Thanks.
Mati_1
30
Newbie Poster
Recommended Answers
Jump to PostI don't know how you'd do that in a query (or if it is even possible. What I would do is go through the table and build a dictionary using val(b1) & "," & val(b2) as the key and the frequency as the value. Then I would scan the dictionary …
Jump to PostAs example:
-- table definition CREATE TABLE `numbers` ( `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `b1` tinyint(3) unsigned DEFAULT NULL, `b2` tinyint(3) unsigned DEFAULT NULL, `b3` tinyint(3) unsigned DEFAULT NULL, `b4` tinyint(3) unsigned DEFAULT NULL, `b5` tinyint(3) unsigned DEFAULT NULL, `b6` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY …
Jump to PostIt should be easy, in practice there are 5 select queries that group the rows by the choosen pairs, in the case below the pair is composed by the columns b1 and b2:
SELECT b1 AS 'x', b2 AS 'y', tot FROM (SELECT b1, b2, COUNT(id) AS …
All 11 Replies
Reverend Jim
4,780
Hi, I'm Jim, one of DaniWeb's moderators.
Moderator
Featured Poster
Mati_1
30
Newbie Poster
Mati_1
30
Newbie Poster
Reverend Jim
4,780
Hi, I'm Jim, one of DaniWeb's moderators.
Moderator
Featured Poster
Mati_1
30
Newbie Poster
Reverend Jim
4,780
Hi, I'm Jim, one of DaniWeb's moderators.
Moderator
Featured Poster
cereal
1,524
Nearly a Senior Poster
Featured Poster
Mati_1
30
Newbie Poster
Mati_1
30
Newbie Poster
cereal
commented:
You're welcome!
+15
cereal
1,524
Nearly a Senior Poster
Featured Poster
Mati_1
30
Newbie Poster
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.