Shafizadeh Shafizadeh - 4 years ago 142
SQL Question

How to select all rows which have identical values

Here is my table:

+----+-------+------+
| id | name | code |
+----+-------+------+
| 1 | jack | 1 |
| 2 | peter | 1 |
| 3 | jack | 1 |
| 4 | ali | 2 |
| 5 | peter | 3 |
| 6 | peter | 1 |
| 7 | ali | 2 |
| 8 | jack | 3 |
| 9 | peter | 2 |
| 10 | peter | 4 |
+----+-------+------+


I want to select all rows that satisfy: the number of {those rows which its
code
value is between 1-3 and its
name
vale is identical} be more or equal than 4

From the above data, I want this output:

+----+-------+------+
| id | name | code |
+----+-------+------+
| 2 | peter | 1 |
| 5 | peter | 3 |
| 6 | peter | 1 |
| 9 | peter | 2 |
+----+-------+------+


How can I do that?

Answer Source

Use a subquery to figure out which names should be returned, then build your main query on that.

Try this:

select *
from mytable
where name in (
    select name
    from mytable
    where code in (1,2,3)
    group by name
    having count(*) > 3)
and code in (1,2,3)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download