CODEWITHSUNDEEP
mysqlsqlmariadb
valar morghulis
valar morghulis

Reputation: 141

Check for distinct values in a row - sql

Given a table with columns (col1,col2,col3,col4,....) , how do we query the table such that it returns only the rows where a specific subset of columns, for example (col2,col4,col5) have values different from each other.

For example for this table(this table is generated after performing some cross-joins and querying), define subset of columns as (t1_id, t2_id, t3_id):

enter image description here

The query should then return this:

enter image description here

The subset of columns will be variable and can be very large hence using sth like where t1.id<>t2.id and t1.id<>t3.id and t2.id<>t3.id wouldn't be a handy approach.

Upvotes: 0

Views: 1033

Answers (2)

LukStorms
LukStorms

Reputation: 29647

A simple solution could be to use NOT IN criteria for N-1 columns.
Which can be shortened for each additional NOT IN.

For example if there are 5 columns :

WHERE t1.id NOT IN (t5.id, t4.id, t3.id, t2.id)
  AND t2.id NOT IN (t5.id, t4.id, t3.id)
  AND t3.id NOT IN (t5.id, t4.id)
  AND t4.id <> t5.id

Another method is to concat the id's and then use a regex.

-- test table with numbers 
create table test (id int primary key);
insert into test values (1),(2),(3),(4),(5);

-- cross joining the numbers and only get those with unique number combinations 
select t1.id as id1, t2.id as id2, t3.id as id3, t4.id as id4, t5.id as id5
from test t1 
cross join test t2 
cross join test t3 
cross join test t4
cross join test t5
where concat_ws(' ',t1.id,t2.id,t3.id,t4.id,t5.id) not rlike '\\b(\\d+)\\b.*\\b\\1\\b';

Tested on dbfiddle for MariaDb 10.2

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The not in approach makes the most sense to me.

However, MariaDB supports PCRE regular expressions. And these support back references. So you can use them to look for duplicates:

where concat(',', concat_ws(',', t1.id, t2.id, t3.id), ',') not regexp '([^,]+).*,\1,'

Note that you might have to double up on the backslash, because it is often an escape character.

Upvotes: 0

Related Questions