Compare current row with previous row in TEradata

Database
Enthusiast

Compare current row with previous row in TEradata

NEED_SQL.JPG

 

can anyone please send me the query for the above scenario 


Accepted Solutions
Senior Apprentice

Re: Compare current row with previous row in TEradata

Seems like you want the 1st row from a new location:

SELECT * FROM tab
QUALIFY
   Coalesce(Min(Location)  -- previous location
            Over (PARTITION BY ID 
                  ORDER BY date 
                  ROWS BETWEEN 1 Preceding AND 1 Preceding), '---') <> Location             

 

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: Compare current row with previous row in TEradata

I think something like this is what you need:

Select ID, Name, Location,
MIN(Date) over(order by date partition by id, name
          reset when Location <> min(location)

          over(order by date partition by id, name rows between 1 preceding and 1 preceding) )

 

Note that RESET WHEN is specific to Teradata.  Perhaps not used very often, but can be really handy when you need it.

Senior Apprentice

Re: Compare current row with previous row in TEradata

Seems like you want the 1st row from a new location:

SELECT * FROM tab
QUALIFY
   Coalesce(Min(Location)  -- previous location
            Over (PARTITION BY ID 
                  ORDER BY date 
                  ROWS BETWEEN 1 Preceding AND 1 Preceding), '---') <> Location             

 

Supporter

Re: Compare current row with previous row in TEradata

sorry - dup answer, was still shown in the unanswerd questionlist...)

code is not tested as you didn't provide test data in an SQL form (e.g. table DDL and inserts)

As I understand you want to filter the rows where the location is different from the previous one.

I would use an OLAP funtion

select *

from yourTable

qualify location <> max(location) over (partiton by id, name order by date rows between 1 preceding and 1 preceding)

or max(location) over (partiton by id, name order by date rows between 1 preceding and 1 preceding) is null;