CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2left-join
Karlx Swanovski
Karlx Swanovski

Reputation: 2989

Last row with joins

Orders
OrderNo    OrderItems   CusID
5001       2            300001
5002       1            300001
5003       2            300002

OrderDetails
OrderDetailsNo     OrderNo   OrderItem
70001              5001      Hotdog
70002              5001      Pancake
70003              5002      Iced Tea
70004              5003      Bacon Sandwich
70005              5003      Ham

I use select top 1 from Orders Order By OrderNo Desc to get the last row if there is no joins.

OrderNo    OrderItems   CusID
5003       2            300002

How will I get the last result of Order with left outer join?

Result

OrderNo    OrderItems   CusID     OrderDetailsNo  OrderItem
5003       2            300002    70004           Bacon Sandwich
5003       2            300002    70005           Ham

Sample Outer join

select  Orders.OrderNo, OrderItems, CusID, OrderDetailsNo, OrderItem 
From Orders
left outer join
OrderDetails
on Orders.OrderNo = OrderDetails.OrderNo

Upvotes: 0

Views: 44

Answers (2)

dnoeth
dnoeth

Reputation: 60482

This should work:

select  Orders.OrderNo, OrderItems, CusID, OrderDetailsNo, OrderItem 
From (select top 1 * from Orders Order By OrderNo Desc) Orders
left outer join
OrderDetails
on Orders.OrderNo = OrderDetails.OrderNo

or

select top 1 with ties Orders.OrderNo, OrderItems, CusID, OrderDetailsNo, OrderItem 
From Orders
left outer join
OrderDetails
on Orders.OrderNo = OrderDetails.OrderNo
Order By Orders.OrderNo Desc

Upvotes: 1

gvee
gvee

Reputation: 17171

; WITH cte AS (
  SELECT TOP (1)
         orderno
       , orderitems
       , CusID
  FROM   orders
  ORDER
      BY orderno DESC
)
SELECT cte.orderno
     , cte.orderitems
     , cte.CusID
     , orderdetails.orderdetailsno
     , orderdetails.orderitem
FROM   cte
 LEFT
  JOIN orderdetails
    ON cte.orderno = orderdetails.orderno

Upvotes: 0

Related Questions