Find Which Rows Have Different Values for a Given Column in Teradata SQL

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

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) .

How to find the distinct records when a value was changed in a table with daily snap shots

Simply use LAG to compare the current and the previous row's color:

SELECT t.*,
   LAG(FAVORITE_COLOR)
   OVER (PARTITION BY CLIENT_ID
         ORDER BY SNAP_EFF_DT) AS prev_color
FROM CUSTOMER_TABLE AS t
QUALIFY
   FAVORITE_COLOR <> prev_color
OR prev_color IS NULL

If your Teradata version doesn't support LAG switch to

   MIN(FAVORITE_COLOR)
   OVER (PARTITION BY CLIENT_ID
         ORDER BY SNAP_EFF_DT
         ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_color

Teradata sql two conditions(and) to check multiple rows to determine validity

You could use INTERSECT:

INTERSECT Operator

Returns only the rows that exist in the result of both queries.

SELECT variety
FROM tab
WHERE color = 'Red'
INTERSECT
SELECT variety
FROM tab
WHERE color = 'Golden'

Teradata SQL: Search for the first time a value changes in a column

select
   CASE WHEN diff <=  5 THEN'<=5 days'
        when diff <= 10 then '6-10 days'
        ELSE '>10 days'
   END as Time_to_fund,
   COUNT(*)
from
 (
    select
       System_Date - Acct_Open_Date as diff -- number of days between
    from mytable
    where Balance > 0 
    qualify -- find the first row with a Balance > 5
       row_number()
       over (partition by Acct_Nbr
             order by System_Date) = 1
 ) as dt

Select distinct rows based on MAX value of a certain column

Use qualify:

SELECT *
FROM tblDATA
QUALIFY ROW_NUMBER() OVER (PARTITION BY PROD_ID ORDER BY VRSN_ID DESC) = 1

QUALIFY is like HAVING or WHERE, except you can use window fucntions.

Filter unwanted characters from a column in Teradata

Thanks Jonas. Since I need only numeric values and the length should be 10, I tried the below and it worked. This would ignore all the additional special characters.

(regexp_similar(Column,'[0-9]{10}')=1)

SQL - Selecting a column value based on max value in another column and combination of values in another column - Teradata

You can use an approach which is similar to the aggregation you tried applying a dirty ol' trick, piggybacking.

You combine both columns into a single string, apply MAX and then strip off the date part again, e.g. for ACCT1 combining both PLAN_DATE & BASE_AMOUNT into a single string will result in this:

'20170101        100'
'20170111         30'
'20170113         50'
'20170118        180'
'20170122         80' -- this will be returned by MAX

After applying max you extract both columns again using SUBSTRING:

   CAST(SUBSTR('2017-01-22         80', 1, 10) AS DATE)
   CAST(SUBSTR('2017-01-22         80', 11) AS INT)

Of course you must create a string which is still sorting the correct way, e.g. yyyymmdd for a date and fixed width including leading spaces for numeric.

Now it's some Cut&Paste&Modify:

SELECT ACCOUNT_NUMBER,
   To_Date(Substr(RK, 1,8), 'yyyymmdd') AS MAX_RK_PLAN_DATE,
   Cast(Substring(RK From 9) AS INT) AS REQUIRED_RK_AMOUNT,
   To_Date(Substr(RC, 1,8), 'yyyymmdd') AS MAX_RC_PLAN_DATE,
   Cast(Substring(RC From 9) AS INT) AS REQUIRED_RC_AMOUNT
FROM 
 ( 
   SELECT ACCOUNT_NUMBER, 
      Max(CASE WHEN PLAN_code IN ('R','K') THEN To_Char(PLAN_DATE, 'yyyymmdd') || BASE_AMOUNT END) AS RK,
      Max(CASE WHEN PLAN_code IN ('R','C') THEN To_Char(PLAN_DATE, 'yyyymmdd') || BASE_AMOUNT END) AS RC
   FROM ACCNT_PLN_INFO
   WHERE  biz_date = DATE '2017-05-31'
   GROUP BY 1
 ) AS dt

---

Related Topics