With a table table1 with columns like:
name, dept, col1, col2, col3, col4, col5
I need to find rows with same values across multiple columns - e.g. col2, col3, col4, col5
While something like below would work, I need an approach that would work even for large number of columns, without having to have the multiple and conditions.
select *
from table1
where col2 = col3 and col3 = col4 and col4 = col5
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);
ALLThis 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]);
SELECT format(
'SELECT * FROM foo WHERE %s;',
(
SELECT string_agg('col1 = col'||id, ' AND ')
FROM generate_series(2,4) AS id
)
);
ALL is the way to go here. Why didn't I think of this earlier? ;-)You can do something like this:
SELECT * FROM table1
WHERE (
SELECT COUNT(*) FROM (
SELECT DISTINCT UNNEST(ARRAY[col2, col3, col4, col5])
) tmp
) = 1;
The steps are broken down here:
Please try below query
SELECT * FROM table1 WHERE col2 IN(col3,col4,col5) OR col3 IN(col2,col4,col5) OR col4 IN(col3,col2,col5) OR col5 IN(col3,col4,col2);
If need any changes let me know.
You could try
SELECT *
FROM table1
WHERE ARRAY_FILL(col2, ARRAY[3]) = ARRAY[col3, col4, col5]
I've created a demo here
ALLor generating sql is the simple solution. See my answer for examples.