Search code examples
azure-data-lakeu-sql

Select top 1 from inner join subquery

I wish to select the first value of a column in the inner join for each successful join. 

@getEndDate = SELECT c.CustomerId, c.ProductId FROM @customer AS c
INNER JOIN (SELECT (DateTime?) EndDate AS EndDate, (int) CustomerId AS CustomerId 
FROM @Installation 
ORDER BY EndDate FETCH 1 ROW OFFSET 0 ROWS) AS i ON c.CustomerId == i.CustomerId

By doing this I get the EndDate lowest EndDate in @Installation where the join ciretia is met.

How can I get only the first value from the inner join subquery for each successful join?

Solution

  • You can apply the FIRST_VALUE function on INNER JOIN SELECT:

    Check this: https://learn.microsoft.com/en-us/u-sql/functions/analytic/first-value