How to Return Rows That Have the Same Column Values in MySQL

Find rows that have the same value on a column in MySQL

This query will give you a list of email addresses and how many times they're used, with the most used addresses first.

SELECT email,
       count(*) AS c
FROM TABLE
GROUP BY email
HAVING c > 1
ORDER BY c DESC

If you want the full rows:

select * from table where email in (
    select email from table
    group by email having count(*) > 1
)

How to return rows that have the same column values in MySql

This is an example of a "sets-within-sets" query. I recommend aggregation with the having clause, because it is the most flexible approach.

select score
from t
group by score
having sum(id = 2) > 0 and -- has id = 2
       sum(id = 4) > 0     -- has id = 4

What this is doing is aggregating by score. Then the first part of the having clause (sum(id = 2)) is counting up how many "2"s there are per score. The second is counting up how many "4"s. Only scores that have at a "2" and "4" are returned.

MySQL Select the rows having same column value

You should be able to accomplish this by joining the table to itself:

SELECT
    a.*
FROM
    employee a
    JOIN employee b
        ON a.empsalary = b.empsalary
        AND a.empid != b.empid

Select rows with same column values (Mysql)

Can you please try:

Select t1.id, t1.ColumnA, t1.ColumnB
From Table t1 
inner join Table t2 on (t1.id <> i2.id AND t1.columnA = t2.columnA AND t1.columnB = t2.columnB)

sql: select rows which have the same column value in another column with a single query

you could try this :

SELECT * 
FROM TABLE my_table 
WHERE id IN (SELECT id 
         FROM TABLE my_table 
         WHERE value = input
         )

sql query to return all rows with same column value

Select ...
From table_name As T
Where Exists    (
                Select 1
                From table_name As T2
                Where T2.column_3 = T.column_3
                    And T2.<Primary Key Column> <> T.<Primary Key Column>
                )

Obviously, you would replace <Primary Key Column> with the column(s) that uniquely identify each row in table_name. If column_1 is that column, then we would get:

Select ...
From table_name As T
Where Exists    (
                Select 1
                From table_name As T2
                Where T2.column_3 = T.column_3
                    And T2.column_1 <> T.column_1
                )

SQL query to find rows with the same value in multiple columns

This is your query:

SELECT * FROM your_table WHERE column_1 = column_2 AND column_2 = column_3

---

Related Topics