CODEWITHSUNDEEP
mysqlcount
Ibrahim Azhar Armar
Ibrahim Azhar Armar

Reputation: 25745

Get most occured value in the table

I got following tables

1. user
+----+-------------------+
| id | email             |
+----+-------------------+
|  2 | [email protected] |
|  3 | [email protected] |
|  1 | [email protected] |
+----+-------------------+

2. answer
+----+---------+-------------+-----------+---------------------+
| id | user_id | question_id | option_id | created             |
+----+---------+-------------+-----------+---------------------+
|  1 |       2 |           1 |         5 | 2015-12-19 15:15:07 |
|  2 |       2 |           1 |         5 | 2015-12-19 15:16:05 |
|  3 |       2 |           2 |         3 | 2015-12-19 15:16:06 |
|  4 |       2 |           3 |         3 | 2015-12-19 15:16:08 |
|  5 |       2 |           1 |         1 | 2015-12-19 15:32:46 |
|  6 |       2 |           1 |         4 | 2015-12-19 15:39:22 |
|  7 |       2 |           1 |         2 | 2015-12-19 15:39:23 |
|  8 |       2 |           1 |         2 | 2015-12-19 15:40:38 |
|  9 |       2 |           1 |         1 | 2015-12-19 15:41:25 |
+----+---------+-------------+-----------+---------------------+

I want to fetch option_id with most occurrences grouped by user with the following condition

  1. If there are two or more maximum occurrences of option_id, get last record.

With reference to above answer table, as you see there are four maximum occurrences for option_id, in this case i want last in the list to be returned which is option_id 1

Here is the query i used to achieve what i want

SELECT
    option_id,
    COUNT(option_id) as occurence
FROM
    answer
GROUP BY
    option_id
ORDER BY 
    occurence DESC LIMIT 1;

This works, however when i add WHERE condition, it gives me option_id 5, whereas i expect option_id 1

SELECT
    option_id,
    COUNT(option_id) as occurence
FROM
    answer
WHERE
    user_id = 2
GROUP BY
    option_id
ORDER BY 
    occurence DESC LIMIT 1;

What am i missing here?

Note : This is a follow-up question from this link, the reason i am re-posting here is to post simplified version of the same question.

Upvotes: 0

Views: 42

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

If it's really only the order_id you are interested in, you can simply add MAX(created) DESC to your ORDER BY:

SELECT
    option_id,
    COUNT(option_id) as occurence
FROM
    answer
WHERE
    user_id = 2
GROUP BY
    option_id
ORDER BY 
    occurence DESC, MAX(created) DESC LIMIT 1;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Step by step:

  1. Count option_id occurrences (group by option_id and count)
  2. Get maximum count from above figures (with max or with order by and limit 1) 3, Get all option_id with this count.
  3. Get all records with one of those option_ids.
  4. Keep only the last record of the this found records (order by limit 1).

Query:

select *
from answer
where option_id in
(
  select option_id
  from answer
  group by option_id
  having count(*) =
  (
    select count(*) as cnt
    from answer
    group by option_id
    order by count(*) desc limit 1
  )
)
order by created desc limit 1;

Upvotes: 1

kofffii
kofffii

Reputation: 21

I did something similar on my tables, and that's my solution:

SELECT option_id
FROM answer 
WHERE id = (SELECT id
            FROM answer 
            WHERE user_id = 2 
            GROUP BY option_id 
            ORDER BY COUNT(option_id) DESC, id DESC
            LIMIT 1)

Or see what happens if you do ORDER BY occurence DESC, id DESC in your query.

Upvotes: 2

Related Questions