Check for distinct values in a row - sql
Multi tool use
Clash Royale CLAN TAG#URR8PPPCheck 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, define subset of columns as (t1_id, t2_id, t3_id):
The query should then return this:
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.
where t1.id<>t2.id and t1.id<>t3.id and t2.id<>t3.id
1 Answer
1
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
How large could that N be? If it's for those cross joins in your previous question? How many cross joins do you think are possible till the database chokes? Btw, are you looking for a standard ANSI SQL solution, or just Oracle?
– LukStorms
2 mins ago
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
I had this idea from your previous answer, however I was wondering if there is a more general approach. Because I will be doing this on a variable input of columns, so lets say if the subset of columns could be between [2,n], I would need to generate (n-1) such if clauses, and in each clause have similar "where" clauses. Too much mess i think.
– nazschi
7 mins ago