Code Example 1:

SELECT Ord.OrderID, Ord.OrderDate,
  (    SELECT MAX(OrdDet.UnitPrice)
       FROM   Northwind.dbo.[Order Details] AS OrdDet
       WHERE  Ord.OrderID = OrdDet.OrderID) AS MaxUnitPrice
  FROM Northwind.dbo.Orders AS Ord

Code Example 2:

SELECT  Ord.OrderID, Ord.OrderDate, 
          MAX(OrdDet.UnitPrice) as maxUnitPrice
  FROM    Northwind.dbo.[Order Details] AS OrdDet 
	inner join 
          Northwind.dbo.Orders AS Ord
  on      Ord.OrderID = OrdDet.OrderID
  group   by Ord.OrderID, Ord.OrderDate

Code Example 3:

SELECT Ord.OrderID, 
         Ord.OrderDate, 
         maxUnitPrice
  FROM   Northwind.dbo.Orders AS Ord inner join
  (
      SELECT orderID,
             MAX(UnitPrice) as maxUnitPrice
      FROM       Northwind.dbo.[Order Details]
      group      by OrderID
  ) AS   OrdDet
  on     ordDet.orderID = Ord.orderID
  order  by Ord.OrderID desc, 
         Ord.OrderDate, 
         maxUnitPrice