CODEWITHSUNDEEP
mysqlsql
nils
nils

Reputation: 27174

MySQL: Getting the highest number of a combination of two fields

I've got two tables: experiments and pairings.

experiments:
-experimentId
-user

pairings:
-experimentId
-tone
-color

Each experiment consists of seven pairings. A pairing consists of matching a color against a tone. And the experiment is repeated multiple times by a single user.

Now I'm trying to find out how to get the highest number of equal pairing per tone. Example:

user  | tone | color | number of equal pairings

user1 | b4   | red   | 5
user1 | c4   | blue  | 4
user2 | b4   | green | 4
…

So far I can get all the equal pairings with the following query:

SELECT user, tone, color, COUNT(tone) as toneCounter
FROM experiments LEFT JOIN pairings ON experiments.experimentId = pairings.experimentId
GROUP BY user, tone, color
ORDER BY toneCounter DESC, user ASC

Which would look like this for example:

user  | tone | color | number of equal pairings

user1 | b4   | red   | 5
user1 | b4   | blue  | 2
user1 | c4   | blue  | 4
user1 | c4   | red   | 1
user1 | c4   | green | 2
user2 | b4   | green | 4
…

Yet I'm not sure how to only get the top equal pairings only. So in the above example I would want to get rid of the other entries for b4 and c4 for user1, and only display b4 red and c4 blue.

I tried it with the following query, but apparently that is not valid SQL:

SELECT user, tone, color, COUNT(tone) as toneCounter
FROM experiments LEFT JOIN pairings ON experiments.experimentId = pairings.experimentId 
GROUP BY user, tone, color
HAVING toneCounter = (select max(COUNT(tone)) as tc from pairings as p where p.tone = pairings.tone)
ORDER BY toneCounter DESC, user ASC

How can I do this?

Upvotes: 1

Views: 165

Answers (3)

Floyd
Floyd

Reputation: 401

2 SQL-Statments, the 2nd should do it... 


SELECT
    AA.user, AA.tone, AA.color, MAX(AA.toneCounter) as toneCounter
FROM (
    SELECT
    user, tone, color, COUNT(tone) as toneCounter
    FROM
    experiments
    LEFT JOIN
    pairings
    ON
    experiments.experimentId = pairings.experimentId 
    GROUP BY
    user, tone, color
) AA
Group by
    AA.user, AA.tone

... my answer did not satisfy myself and I doublechecked it. And I think the next answer is more adequate (and even runs on no-mysql)


SELECT 
    AAA.user, AAA.tone, BBB.color, AAA.toneCounter 
FROM (
    SELECT
    AA.user, AA.tone, MAX(AA.toneCounter) as toneCounter
    FROM (
    SELECT
        user, tone, color, COUNT(tone) as toneCounter
    FROM
        experiments
    LEFT JOIN
        pairings
    ON
        experiments.experimentId = pairings.experimentId 
    GROUP BY
        user, tone, color
    ) AA
    Group by
    AA.user, AA.tone
) AAA
join (
    SELECT
    BB.user, BB.tone, BB.color, MAX(BB.toneCounter) as toneCounter
    FROM (
    SELECT
        user, tone, color, COUNT(tone) as toneCounter
    FROM
        experiments
    LEFT JOIN
        pairings
    ON
        experiments.experimentId = pairings.experimentId 
    GROUP BY
        user, tone, color
    ) BB
    Group by
    BB.user, BB.tone, BB.color 
) BBB
ON
    BBB.user = AAA.user
    AND BBB.tone = AAA.tone 
    AND BBB.toneCounter = AAA.toneCounter

Upvotes: 2

HellBaby
HellBaby

Reputation: 580

create table experiments(
experimentId int identity(1,1),
[user] nvarchar(256));

create table pairings(
experimentId int,
tone nvarchar(256),
color nvarchar(256));

insert into experiments([user]) values ('user1');
insert into experiments([user]) values ('user1');
insert into experiments([user]) values ('user1');
insert into experiments([user]) values ('user2');
insert into experiments([user]) values ('user1');
insert into experiments([user]) values ('user1');
insert into experiments([user]) values ('user1');

insert into pairings(experimentId, tone,color) values (1,'b4','red');
insert into pairings(experimentId, tone,color) values (2,'b4','red');
insert into pairings(experimentId, tone,color) values (3,'c4','green');
insert into pairings(experimentId, tone,color) values (4,'c4','blue');
insert into pairings(experimentId, tone,color) values (5,'b4','red');
insert into pairings(experimentId, tone,color) values (6,'b4','green');
insert into pairings(experimentId, tone,color) values (7,'b4','green');

IF OBJECT_ID('tempdb..#tempData') IS NOT NULL DROP TABLE #tempData;
IF OBJECT_ID('tempdb..#tempTones') IS NOT NULL DROP TABLE #tempTones;
select  a.[user],b.tone,b.color,count(b.tone) as toneCounter into #tempData
from experiments a inner join pairings b on a.experimentId=b.experimentId
group by a.[user],b.tone,b.color
order by toneCounter desc,a.[user] asc;

select distinct [user],tone into #tempTones from #tempData;
with cte as(
select a.[user],a.[tone],max(a.toneCounter) as toneCounter from #tempData a inner join 
 #tempTones b on (b.[user]=a.[user] and b.tone=a.tone) group by a.tone,a.[user]
)
select a.* from #tempData a inner join cte b on 
(b.[user]=a.[user] and a.toneCounter=b.toneCounter and a.tone=b.tone)

Upvotes: 1

Chun Lin
Chun Lin

Reputation: 544

If I understand your question correctly, what I will do is, firstly, retrieving the maximum tone counter of each tone for each user from the result table you got. Secondly, I will use that info to left join with the same result table you got to get the final result.

SELECT OriRef.* 
FROM
(
    SELECT user, tone, MAX(toneCounter) AS maxToneCounter
    FROM
    ( 
        SELECT user, tone, color, COUNT(tone) as toneCounter
        FROM experiments LEFT JOIN pairings ON experiments.experimentId = pairings.experimentId 
        GROUP BY user, tone, color
    ) AS Ref
) AS MaxRef
LEFT JOIN
( 
    SELECT user, tone, color, COUNT(tone) as toneCounter
    FROM experiments LEFT JOIN pairings ON experiments.experimentId = pairings.experimentId 
    GROUP BY user, tone, color
) AS OriRef ON MaxRef.user = OriRef.user AND MaxRef.tone = OriRef.tone AND MaxRef.maxToneCounter = OriRef.toneCounter

Please correct me if I'm wrong.

Upvotes: 1

Related Questions