How to get the value of a column's entry where the other column's value in maximum in SQL?

Question

So I have Two tables

One is "Orders" containing following columns

1. OrderID (int)(primary key)
2. CustomerID (nchar(5))
3. OrderDate (Datetime)

Second is "Order Details" containing following columns

1. OrderID (int)
2. ProductID (int) 1 and 2 combined is the primary key for this table.
3. UnitPrice (money)
4. Quantity (smallint)
5. Discount (real)

Now write a query to find out the OrderId, ProductId, OrderDate and UnitPrice of the products having highest price in an order.

The price of product is calculated as (UnitPriceQuantity - DiscountUnitPrice*Quantity). The operation has to be performed for each OrderID in "Orders".

I am putting up Sample Data.Here is Input table "Orders"-

OrderID  CustomerID  OrderDate

1000     1           11-09-2016
1001     1           12-10-2016

Here is Another Input table "Order Details"-

OrderID  ProductID  UnitPrice  Quantity  Discount

1000     11         5          15        0.0
1000     13         25         2         0.0
1000     14         7          11        0.0
1001     5          30         3         0.0
1001     6          50         2         0.12

Here is the expected Output

OrderID  ProductID  OrderDate   UnitPrice

1000     14         11-09-2016  7
1001     5          12-10-2016  30

I have already tried it using Joins. Here is the query I have tried

DECLARE @OID INT=10248;

SELECT d.OrderID,d.ProductID,d.UnitPrice 
INTO #Temp3
from [Order Details] d
INNER JOIN
(Select TOP 1 OrderID,ProductID,MAX(UnitPrice*Quantity-UnitPrice*Discount*Quantity)as Maxi from [Order Details] 

    where OrderID=@OID  
    GROUP BY  OrderID,ProductID
     ORDER BY Maxi DESC) e ON e.OrderID=d.OrderID and e.ProductID=d.ProductID;

select T.OrderID,T.ProductID,o.OrderDate,T.UnitPrice
from Orders o
INNER JOIN #Temp3 T
ON o.OrderID=T.OrderID;

The only problem with this query is that I have to provide the OrderID. I want it to be executed for every OrderID in "Orders"

Answer 1

So you want to rank your order details per order (and only show the best ranked records, i.e. those with the highest total price).

select o.orderid, od.productid, o.orderdate, od.unitprice 
from orders o
join
(
  select
    orderid, 
    productid, 
    unitprice,
    rank() over (partition by orderid order by unitprice * quantity desc) as rnk
  from order_details
) od on od.orderid = o.orderid and od.rnk = 1
order by o.orderid;

Answer 2

You can use below query

SELECT OD.OrderId, OD.ProductId, O.OrderDate, OD.UnitPrice, OD1.price  FROM Orders O
INNER JOIN Order_Details OD
ON (O.OrderID = OD.OrderID)
INNER JOIN (SELECT MAX(UnitPrice*Quantity) as price ,OrderID ,ProductID FROM Order_Details 
GROUP BY OrderID ,ProductID) OD1 
ON (O.OrderID = OD1.OrderID);