Find which rows have different values for a given column in Teradata SQL

Question

I am trying to compare two addresses from the same ID to see whether they match  For example   Id  Adress Code     Address 1   1               123 Main 1   2               123 Main 2   1               456 Wall 2   2               456 Wall 3   1               789 Right 3   2               100 Left   I m just trying to figure out whether the address for each ID matches  So in this case I want to return just ID 3 as having a different address for Address Code 1 and 2

User · Answer

This works for PL SQL   select count     id address from table group by id address having count    lt 2

User · Answer

Join the table with itself and give it two different aliases  A and B in the following example   This allows to compare different rows of the same table   SELECT DISTINCT A Id FROM     Address A     INNER JOIN Address B         ON A Id   B Id AND A  Adress Code   lt  B  Adress Code  WHERE     A Address  lt  gt  B Address   The  less than  comparison  lt  ensures that you get 2 different addresses and you don t get the same 2 address codes twice  Using  not equal   lt  gt  instead  would yield the codes as  1  2  and  2  1   each one of them for the A alias and the B alias in turn   The join clause is responsible for the pairing of the rows where as the where-clause tests additional conditions     The query above works with any address codes  If you want to compare addresses with specific address codes  you can change the query to  SELECT A Id FROM     Address A     INNER JOIN Address B         ON A Id   B Id WHERE                          A  Adress Code    1 AND     B  Adress Code    2 AND     A Address  lt  gt  B Address   I imagine that this might be useful to find customers having a billing address  Adress Code   1 as an example  differing from the delivery address  Adress Code   2

User · Answer

Personally  I would print them to a file using Perl or Python in the format   lt COL NAME gt     lt COL VAL gt    for each row so that the file has as many lines as there are columns   Then I d do a diff between the two files  assuming you are on Unix or compare them using some equivalent utilty on another OS   If you have multiple recordsets  i e  more than one row   I would prepend  to each file row and then the file would have NUM DB ROWS   NUM COLS lines

User · Answer

You can do this using a group by   select id  addressCode from t group by id  addressCode having min address   lt  gt  max address    Another way of writing this may seem clearer  but does not perform as well   select id  addressCode from t group by id  addressCode having count distinct address   gt  1

[sql] Find which rows have different values for a given column in Teradata SQL

Examples related to sql

Examples related to join

Examples related to teradata

Examples related to rows

Examples related to matching