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

Question

I want to find rows in my table that have duplicate values accross columns in a the same row

Example:

id   column_1    column_2    column_3
1    123            44           100
2    555            555          555
3    101            396          100
4    99             99           99
5    123            44           100

I need a query that returns rows 2 & 4. So far, I have only found questions with similar titles that refer to finding rows that have the same values in multiple columns that would for example return 1 & 5. That's not what I am looking for :)

where col1=col2 and col2=col3

Vamsi Prabhala
– Vamsi Prabhala

2018-05-29 16:27:55 +00:00
Commented May 29, 2018 at 16:27 — Vamsi Prabhala
– Vamsi Prabhala, Commented May 29, 2018 at 16:27

Héctor Valls · Accepted Answer · 2018-05-29 16:29:04Z

1

This is your query:

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

answered May 29, 2018 at 16:29

Héctor Valls

26.4k43 gold badges153 silver badges268 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barbaros Özhan · Accepted Answer · 2018-05-29 18:05:35Z

1

Substraction may be used for three columns :

select *
  from mytable
 where ( column_1 - column_2  =  column_3 - column_2 )
   and column_1 = column_2
order by 1;

or least and greatest functions may be used together :

select *
  from mytable    
 where greatest(column_1,column_2,column_3) = least(column_1,column_2,column_3)
order by 1;

SQL Fiddle Demo

edited May 29, 2018 at 18:05

answered May 29, 2018 at 16:29

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

2 Comments

dnoeth Over a year ago

least and greatest are not aggregate functions. No need for group by in your 2nd query, it's a simple WHERE greatest(column_1,column_2,column_3) = least(column_1,column_2,column_3)

Barbaros Özhan Over a year ago

@dnoeth exactly, danke schön.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related