Limit occurrences of column

I'm having a little bit of trouble writing a query, and I was hoping someone could help me out. I'm trying to query the number of entries in a database while limiting the number of duplicates in a column to x.

| a_id | important |
---------------------------
| 0001 |      'a' |
| 0002 |      'b' |
| 0003 |      'c' |
| 0004 |      'a' |
| 0005 |      'a' |
| 0006 |      'a' |
| 0007 |      'b' |
| 0008 |      'b' |

Code (markup):

In the table above, let's say I want to limit the query to yield only all rows such that there are no more than 2 duplicates amongst the column "important".

Results:

| a_id | important |
---------------------------
| 0001 |      'a' |
| 0002 |      'b' |
| 0003 |      'c' |
| 0004 |      'a' |
| 0007 |      'b' |

Code (markup):

Is this query even possible?


Thanks in advance!

 

You could limit it to 1 using a GROUP BY in the query. I can't think of a way to limit it to 2 of one type through.

 

What you are trying to do turned out to be really hard though not impossible. You are basically trying to limit the result of the group by clause to 2 instead of the unique 1. And not just any 2 but the top 2.

If you really want to do this in one quiz maybe something like this can work (assuming X is 2):


SELECT
important, a_id
FROM (
SELECT
*,
@im := CASE WHEN @prev_important = important THEN @im + 1 ELSE 1 END AS im,
@prev_important := important
FROM tableName AS T1, (SELECT @prev_important := '', @im := 0) AS vars
ORDER BY important, a_id ASC
) AS T2
WHERE im <= 2
ORDER BY important, a_id ASC