Search code examples
sqlsql-serversubquery

SQL Server - Query - How to get the last result of a subquery?

I have to data tables. The first one with users:

1

and the second with their orders.

2

Is it possible to get the whole list of users with their respective last order ? Like this:

3

This was my try, but isn't allowed:

select a.id  ,a.Name , b.Order ,b.Date
from users as a
left join (
  select  top 1 Orders.id  , Orders.order , Orders.Date  
  from Orders
  where Orders.id =  a.id
  order by orders.Date desc
  ) as b
  on a.ID = b.Id
Solution

  • one way would be to use window functions:

    select * 
from Users u 
left join ( 
   select * , row_number() over (partition by ID order by date desc) rn
   from orders 
) t on t.rn = 1 and u.id = t.id