Select rows with most frequent values of one column MySQL

Question

I'd like to select all rows from a table which contain 50 most frequent values of the column. I tried to use such a join, but it seems my choice of LEFT JOIN is wrong. The inner part of the statement seems fine. What should I change in my statement?

SELECT col1, col2 
FROM tbl as t1 
LEFT JOIN (
    SELECT id 
    FROM tbl 
    WHERE id > 123 
      AND id < 987654 
    GROUP BY col1 
    ORDER BY COUNT(id) DESC 
    LIMIT 50
) AS t2 
ON t1.id = t2.id

What's wrong with your query? Have you tried changing it to an `inner join` instead? — sgeddes, Aug 08 '16 at 16:33
Inner join returns just 50 rows ofc. The weird thing is when I changed the limit inside the inner select to 5, the number of rows returned didn't change. — pedmillon, Aug 08 '16 at 16:36
When using the `left join`, the results would be the same, some would just have a `null` id from `t2` (if you were displaying that). Perhaps sample data and expected results would help... — sgeddes, Aug 08 '16 at 16:38

score 0 · Answer 1 · answered Aug 08 '16 at 16:54

0

Rather than a JOIN, have you tried using an IN operator as part of your WHERE clause?

For example...

SELECT col1, col2 
FROM tbl as t1 
WHERE t1.id IN (
    SELECT id 
    FROM tbl 
    WHERE id > 123 
        AND id < 987654 
    GROUP BY col1 
    ORDER BY COUNT(id) DESC 
    LIMIT 50
)

answered Aug 08 '16 at 16:54

Sean

747
4
8

For some reason I can't use LIMIT inside the IN clause – pedmillon Aug 08 '16 at 22:15

Viet Trang · Answer 2 · 2016-08-08T17:49:14.817

0

Right join should work if your subquery is correct, returning any matching rows of t1 to t2 (as opposed to all rows of t1 and the matching t2's or nulls):

SELECT col1, col2 
FROM tbl as t1 
RIGHT JOIN (
    SELECT id 
    FROM tbl 
    WHERE id > 123 
      AND id < 987654 
    GROUP BY col1 
    ORDER BY COUNT(id) DESC 
    LIMIT 50
) AS t2 
ON t1.id = t2.id

edited Aug 08 '16 at 17:49

answered Aug 08 '16 at 17:31

Viet Trang

51
5

score 0 · Answer 3 · answered Aug 09 '16 at 00:40

I realize my query was OK, I just wanted to join on col1, not id :)

SELECT col1, col2 
FROM tbl as t1 
LEFT JOIN (
  SELECT col1 
  FROM tbl 
  WHERE id > 123 
  AND id < 987654 
  GROUP BY col1 
  ORDER BY COUNT(id) DESC 
  LIMIT 50
) AS t2 
ON t1.col1 = t2.col1

Select rows with most frequent values of one column MySQL

3 Answers3