CODEWITHSUNDEEP
mysqlsql
tamao
tamao

Reputation: 25

Show all rows in MySQL that contain the same value (specific value)

i have MYSQL database 

id | value1 | value2
---+--------+-------
1  |    1   |   1
2  |   10   |   3
3  |   10   |   3
4  |   10   |   2
5  |   11   |   3
6  |   11   |   2
7  |   12   |   1
8  |   13   |   4

How would I return a list of all the columns where the same value1 appears more than once but specific value, eg, I'd like to return with value1 = 10:

value1 | value2
  10   |   3
  10   |   3
  10   |   2

I have use count query like this:

SELECT value1,value2 FROM table WHERE value1 in ( SELECT value1 FROM table GROUP BY value1=10 having count(*) > 1 )

but the return like this

value1 | value2
   1   |   1
  10   |   3
  10   |   3
  10   |   2

why return come with value1=1 ?

any idea? thx.

Upvotes: 1

Views: 819

Answers (2)

spencer7593
spencer7593

Reputation: 108410

It's returning that row with value1 = 1 because of the 

GROUP BY value1=10

That's specifying grouping by the result of a boolean expression, which returns 1 (TRUE), 0 (FALSE) or NULL for each row.

To get the count of rows that have the same value for value1, you'd want 

GROUP BY value1

If you only want to return rows where value1 is equal to 10, you could include a predicate in a WHERE clause

WHERE value1 = 10

If I were going to return the specified result set, I'd do it with a query something like this:

SELECT t.value1
     , t.value2
  FROM ( SELECT r.value1
           FROM table r
          WHERE r.value1 = 10
          GROUP BY r.value1
         HAVING COUNT(1) > 1
       ) s
  JOIN table t
    ON t.value1 = s.value1

If I wanted to use a subquery in a predicate (instead of a join operation) I could do something like this:

SELECT t.value1, t.value2
  FROM tableX t
 WHERE t.value1 = 10
   AND (SELECT COUNT(*) FROM tableX r WHERE r.value1 = 10) > 1

The previous query with join pattern can easily be extended to return multiple values, we could replace the WHERE r.value = 10 with WHERE r.value IN (10,20,30). With the second query, with the subquery in the predicate, that can't be as easily extended, not without a (potentially) light dimming correlated subquery.

Upvotes: 1

Maxqueue
Maxqueue

Reputation: 2444

try this: SELECT value1,value2 FROM table WHERE value1 in ( SELECT value1 FROM table where value1 = 10) and (select count(*) where value1 = 10)>1

Upvotes: 0

Related Questions