CODEWITHSUNDEEP
sqlsql-server
maggotknx
maggotknx

Reputation: 37

Get latest record for each entry

I have a table which says when a product was replaced for other and this other product may also appear in this table.

I want to know how can I write a query where given a product, I get the latest available (replaced) product.

Example:

    Product_From   Product_To
    -------------------------
    A              E
    A              D
    B              F
    C              B
    D              F
    D              G

How would be a query to get the following output:

    Product_From   Product_Final
    -------------------------
    A              E
    A              F
    A              G
    B              F
    C              F
    D              F
    D              G

The product A was replaced for product D and E but product D was replaced for F and G so product A has products E, F & G as "final products".

The problem is that products can have different replacement entries thus I don't know how many JOINS I need...

Upvotes: 2

Views: 55

Answers (2)

forpas
forpas

Reputation: 164194

You can do it with self left join:

select 
  p1.product_from,
  coalesce(p2.product_to, p1.product_to) product_final 
from products p1 left join products p2 
on p2.product_from = p1.product_to

See the demo.
Results:

> product_from | product_final
> :----------- | :------------
> A            | E            
> A            | F            
> A            | G            
> B            | F            
> C            | F            
> D            | F            
> D            | G

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This is a graph-walking problem. A recursive CTE is the solution:

with p as (
      select v.*
      from (values ('A', 'E'), ('A', 'D'), ('B', 'F'), ('C', 'B'), ('D', 'F'), ('D', 'G')) v(pfrom, pto)
     ),
     cte as (
      select pfrom, pto
      from p
      where not exists (select 1 from p p2 where p2.pfrom = p.pto)
      union all
      select p.pfrom, cte.pto
      from cte join
           p
           on cte.pfrom = p.pto
     )
select *
from cte
order by pfrom, pto;

Here is a db<>fiddle.

Upvotes: 1

Related Questions