How to find lowest data among all updated data.

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Visitor

How to find lowest data among all updated data.

Hi,

 

I have data in atable like below.

 

prev_loan_nr  current_loan_nr

------------  ---------------

           1                2

           2                3

           3                4

          11               21

          21               31

          31               41

 

I need to select data from above table like below.

 

 

prev_loan_nr    current_loan_nr        Starting loan_number
------------        ---------------             --------------------

           1                2                         1

           2                3                         1

           3                4                         1

          11               21                       11

          21               31                       11

          31               41                       11

 

Here we need previous loan number which is the starting loan_number if we will search backword for each record.

Note : In the table there is no other columns.

 

Thanks,

Nabakishore


Accepted Solutions
Junior Contributor

Re: How to find lowest data among all updated data.

You need a recursive query:

WITH RECURSIVE cte AS
 (  -- start with 1 loan, i.e. no previous record of loan number found
   SELECT t.*, prev_loan_nr AS start_loan_nr
   FROM vt AS t
   WHERE NOT EXISTS
    (  
      SELECT *
      FROM vt AS t2
      WHERE t2.current_loan_nr = t.prev_loan_nr
    )
   UNION ALL -- find the next row 
   SELECT t.*, cte.start_loan_nr
   FROM cte JOIN vt AS t
   ON cte.current_loan_nr = t.prev_loan_nr
 )
SELECT * FROM cte

 

1 ACCEPTED SOLUTION
1 REPLY
Junior Contributor

Re: How to find lowest data among all updated data.

You need a recursive query:

WITH RECURSIVE cte AS
 (  -- start with 1 loan, i.e. no previous record of loan number found
   SELECT t.*, prev_loan_nr AS start_loan_nr
   FROM vt AS t
   WHERE NOT EXISTS
    (  
      SELECT *
      FROM vt AS t2
      WHERE t2.current_loan_nr = t.prev_loan_nr
    )
   UNION ALL -- find the next row 
   SELECT t.*, cte.start_loan_nr
   FROM cte JOIN vt AS t
   ON cte.current_loan_nr = t.prev_loan_nr
 )
SELECT * FROM cte