Menu
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.
969
DISTINCT Function - Teradata Instead of contemplating a WHERE clause to accomplish this task, the DISTINCT can be added in the SELECT to return unique values by eliminating duplicate values. The syntax for using DISTINCT: SELECT DISTINCT <column-name> [, <column-name> ... ]
In SQL, problems require us to compare two columns for equality to achieve certain desired results. This can be achieved through the use of the =(equal to) operator between 2 columns names to be compared.
To calculate a difference, you need a pair of records; those two records are “the current record” and “the previous year's record”. You obtain this record using the LAG() window function. This function allows you to obtain data from the previous record (based on an order criterion, which here is “ ORDER BY year ”).
LIKE clause is used to perform the pattern matching task in SQL. A WHERE clause is generally preceded by a LIKE clause in an SQL query. LIKE clause searches for a match between the patterns in a query with the pattern in the values present in an SQL table.
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] < B.[Adress Code] WHERE A.Address <> B.Address The "less than" comparison < ensures that you get 2 different addresses and you don't get the same 2 address codes twice. Using "not equal" <> 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 <> 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) .
182
This works for PL/SQL:
select count(*), id,address from table group by id,address having count(*)<2 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
