Filter Results In Sql

Suppose I have a table id value ------ --------- 10 123 10 422 11 441 11 986 12 674

Solution 1:

select a2.*from MyTable a2
innerjoin
(
select a1.id
from MyTable a1
groupby a1.id
havingcount(*) >1
) a3
on a3.id = a2.id

Solution 2:

Assuming a UNIQUE KEY can be formed on (id,value)...

SELECTDISTINCT x.*
           FROM my_table x
           JOIN my_table y
             ON y.id = x.id
            AND y.value <> x.value

If a UNIQUE KEY cannot be formed on (id,value), then this isn't really a table in a strict RDBMS sense.

Solution 3:

You can use this query :

SELECT*fromtablewhere id in 
( SELECT id FROMtablegroupby id havingcount(id) >1 )

Solution 4:

With mysql 8+ or mariadb 10.2+, you would use the count window function:

select id, valuefrom (
    select id, value, count(id) over (partitionby id) as num_values
    from sometable
) foo
where num_values >1;