Finding Rows with Same Values in Multiple Columns

Finding rows with same values in multiple columns

Try the following:

SELECT A.*
FROM YourTable A
INNER JOIN (SELECT Address, State
            FROM YourTable
            GROUP BY Address, State
            HAVING COUNT(*) > 1) B
ON A.Address = B.Address AND A.State = B.State

Select rows having multiple columns of same value

Sample data..

CREATE TABLE foo
AS
  SELECT id,
    trunc(random()*10) AS col1,
    trunc(random()*10) AS col2,
    trunc(random()*10) AS col3,
    trunc(random()*10) AS col4
  FROM generate_series(1,1e6)
    AS gs(id);

Using ALL

This method is massively shorter, but you still have to type all of the column names once.

SELECT * FROM foo
WHERE col1 = ALL(ARRAY[col2,col3,col4]);

Dynamic sql..

SELECT format(
  'SELECT * FROM foo WHERE %s;',
  (
    SELECT string_agg('col1 = col'||id, ' AND ')
    FROM generate_series(2,4) AS id
  )
);

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

How to find rows with the same values in multiple columns in mysql

Using aggregation, we can try:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT SUBJECT_ID, PREDICATE_ID, OBJECT_ID
    FROM yourTable
    GROUP BY SUBJECT_ID, PREDICATE_ID, OBJECT_ID
    HAVING COUNT(*) > 1
) t2
    ON t1.SUBJECT_ID = t2.SUBJECT_ID AND
       t1.PREDICATE_ID = t2.PREDICATE_ID AND
       t1.OBJECT_ID = t2.OBJECT_ID;

If you are using MySQL 8+, we can leverage analytical functions for a cleaner looking query:

WITH cte AS (
    SELECT *, COUNT(*) OVER (PARTITION BY BY SUBJECT_ID, PREDICATE_ID, OBJECT_ID) cnt
    FROM yourTable
)

SELECT *
FROM cte
WHERE cnt > 1;

Retrun True for rows with same values in multiple columns in SQL

Do a GROUP BY. If the max(code) = min(code), they are equal.

select id, max(code) = min(code)
from tablename
group by id

You can also use count() instead, for example if the rows don't come in pairs.

select id, count(distinct code) = 1
from tablename
group by id

How to select all rows where a multiple column value occurs more than once in postgresql?

Use window function - count as follows

select * from
(select t.*,
       count(1) over (partition by product_id, entry_Date) as cnt
  from t) t
where cnt > 1

You can also use exists as follows:

select * from t
where exists
(select 1 from t tt
where t.product_id = tt.product_id
  and t.entry_Date = tt.entry_date
  and t.id <> tt.id)

---

Related Topics