CODEWITHSUNDEEP
mysqlsql
ajbee
ajbee

Reputation: 3641

Return the values of max count of rows with the same values

I have table_schedule(id,instructorNumber,time,date,cpNumber,user_id);

Is it possible to output the values of instructorNumber,time, etc. with the same highest occurence with same values?

sorry I am new in sql, so I am hoping that someone can help me to the query

Upvotes: 1

Views: 2065

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

First group by the values you want to compare by and count. Get the maximum count (in MySQL you can use LIMIT for that). Then do the same group-by query again and take only the results HAVING the maximum count. Use GROUP_CONCAT to get a list of IDs in a string.

select instructorNumber, time, date, cpNumber, user_id, group_concat(id) as ids
from table_schedule
group by instructorNumber, time, date, cpNumber, user_id
having count(*) =
(
  select count(*)
  from table_schedule
  group by instructorNumber, time, date, cpNumber, user_id
  order by count(*) desc limit 1
);

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

Just group by all the fields you need for "same value comparison", order by count desc (so the result with most occurences will be first), and take first.

select 
instructorNumber, time, date, cpNumber
from table_schedule
group by instructorNumber, time, date, cpNumber
order by count(*) desc
LIMIT 1

you may use this as a join on a main query if you want more than one result.

Upvotes: 1

Related Questions