CODEWITHSUNDEEP
mysql
Skat0r
Skat0r

Reputation: 55

Filter a mysql query with conditions / Group the result

I have a mysql table (see below) which contains a lot email addresses. I want to select X(20) entries from this table but only Y(2) from each domain. Is this possible with sql? If I use Group by only one domain will be used. But it should be variable how many domains are used per Query.

The Table

id | email        | domain
---|--------------|--------
1  | [email protected]   | bar.de
2  | [email protected]   | bar.de
3  | [email protected] | bar.de
4  | [email protected]  | bar.de
5  | [email protected]  | foo.com
6  | [email protected]  | foo.com

The Result should be

ID: 1,2,5,6

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If you only want two entries for each domain, then you can do:

select t.*
from thetable t
where (select count(*)
       from thetable t2
       where t2.domain = t.domain and t2.id <= t.id
      ) <= 2;

If you have a larger table, there are more efficient methods.

I have no idea what X(20) is supposed to mean.

Upvotes: 1

Related Questions