0

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
eshirvana
  • 23,227
  • 3
  • 22
  • 38
Marc
  • 3
  • 1
  • 3
    Please read : [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) – MatBailie May 31 '22 at 13:57
  • 1
    Hello Marc and welcome to StackOverflow. You'll find that images are not the recommended way to present data here that has a purely text content, especially when it is important for people to reproduce your results. **Linked** images are even more unwelcome than inline images. – Ross Presser May 31 '22 at 13:57
  • @jarlh - `OUTER APPLY` in this case. – MatBailie May 31 '22 at 13:59
  • @MatBailie, you're correct! (I've never used SQL Server.) – jarlh May 31 '22 at 14:00

1 Answers1

1

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
eshirvana
  • 23,227
  • 3
  • 22
  • 38