Find Rows That Have the Same Value on a Column 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 find rows with exact same value in one or more columns

Try this:

SELECT ID, number, value
FROM mytable
WHERE value IN (SELECT value
                FROM mytable
                GROUP BY value
                HAVING COUNT(*) >= 2)

and this for the second case:

SELECT ID, number, value
FROM mytable
WHERE (number, value) IN (SELECT number, value
                          FROM mytable
                          GROUP BY number, value
                          HAVING COUNT(*) >= 2)

SELECT rows having same value in two fields, and different value in another

I want to select rows that have the same value in column1 and column2 but differ in column3.

Use exists:

select t.*
from my_table t
where exists (select 1
              from my_table t2
              where t2.column1 = t.column1 and t2.column2 = t.column2 and
                    t2.column3 <> t.column3
             );

Your sample results, though, suggest that you just want column1/column2 pairs. If so:

SELECT column1, column2
FROM my_table
GROUP BY column1, column2
HAVING MIN(column3) <> MAX(column3);

Your version of the query just returns column1/column2 pairs that have a column3 value with more than one row.

How to select rows that have the same value in a column, without knowing that value in advance?

You could join both queries:

SELECT id, title
FROM   records a
JOIN   records b ON a.docId = b.docId AND a.id < b.id
WHERE  b.id = 4;

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

Find rows that have the same value on a column in asp.net

You can try out both ways.

Using Lambda Expressions

            db.StudentTeachers.GroupBy(s => s.StudentId)
                  .Where(grp => grp.Count() > 1)
                  .Select(grp => new
                  {
                      StudentCount = grp.Count(),
                      StudentId = grp.Key
                  }).ToList();

Using SQL like query expressions

from s in db.StudentTeachers
group s by s.StudentId into grp
where grp.Count() > 1
select new { StudentId = grp.Key, StudentCount = grp.Count() }).ToList();

Find rows that have the same value and select the newer one

On MySQL 8+, we can use ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY sensorNr ORDER BY ModifyDate DESC) rn
    FROM yourTable
)

SELECT serialNr, sensorNr, ModifyDate
FROM cte
WHERE rn = 1;

get rows that have the same value column

I think you are looking for below (at least this is a direct translation of your original query to one that actually works while preserving the logic)

select id, path
from `bigquery-public-data.github_repos.sample_files` 
qualify count(id) over(partition by path) > 1

---

Related Topics