Alternative for Cross Apply() in SQL

Question

How can the below SQL query written using JOIN? I'm trying to convert SQL script to Trino/Presto and this does not support Cross Apply().

 select i.*,k.*

  FROM dbo.[LN] i
  CROSS APPLY(select top 1 * from
  dbo.[LN]  j
  where i.vnd_nbr = j.vnd_nbr and i.cltstyle = j.cltstyle and j.compdate > i.compdate order by compdate
  ) k order by i.VND_NBR

Top 1 means you have multiple rows, but a Top without an order makes noch much sense — nbk, Aug 20 '22 at 19:38
@AaronBertrand eventually it does, as I'm currently working with handful of records only. — Vineet Nair, Aug 20 '22 at 19:56
@AaronBertrand definitely not. Currently I'm in process of migrating the SQL script to Trino/Presto, but sadly it does not support Cross Apply(). — Vineet Nair, Aug 20 '22 at 20:00
@AaronBertrand reaaly not sure. There are many SQL methods which are not supported in Trino/Presto.. always have to find a work around in such cases. — Vineet Nair, Aug 20 '22 at 20:24

score 2 · Answer 1 · answered Aug 20 '22 at 20:12

As you have a system that does not suport CROSS APPLY

xou can use a CTE for that purpüose

WITH CTE AS(
 select i.*,k.*,
 ROW_NUMBBER() OVER (PARTITION BY k.vnd_nbr,k.cltstyle, k.compdate ORDER BY k.compdate) en
  FROM dbo.[LN] i
  JOIN 
  dbo.[LN]  k
  ON i.vnd_nbr = k.vnd_nbr and i.cltstyle = k.cltstyle and k.compdate > i.compdate
  )  
  SELECT * FROM 
  CTE
  WHERE rn = 1
  order by VND_NBR

Alternative for Cross Apply() in SQL

1 Answers1

Linked