Distinct records when joined with a table in SQL Server

Question

How do I get top 1 record for each product from the query below?

  SELECT DISTINCT o.product, o.orderID, od.qty, od.amount FROM
  orders o, orderdetails od WHERE o.orderID=od.orderID


Product  OrderID   Qty   Amount
Pen       11222    10     100.00
Pen       11223     5      50.00
Pen       11224     1      10.00
Book      22222     1      12.00
Book      2223      5      10.00
Scale     1111      2       9.00
Scale     2222      1       2.00

Answer 1

SELECT 
    o.Product, MAX(od.QTy) 
FROM 
    orders o
    INNER JOIN orderdetails od ON o.orderID=od.orderID
GROUP BY o.Product

or

SELECT 
    o.Product, MAX(od.Amount) 
FROM 
    orders o
    INNER JOIN orderdetails od ON o.orderID=od.orderID
GROUP BY o.Product

depending on what you consider as "top" product.

Answer 2

Top based on what? Most Quantity? if so try this

  Select * From Orders O
  Where OrderId = 
     (Select Max(orderId) From Orders
      Where product = P.product
        And Qty = (Select Max(Qty) 
                   From orders 
                   Where Product = O.Product))

Otherwise if Top means something else change last three lines to find Orderid for the record that satisfies whatever that definition of 'Top'

Answer 3

Give this a shot...

Select o.product, o.orderID, od.qty, od.amount 
from orders o, orderdetails od 
where o.orderID = od.orderID
   and o.orderID in (Select Min(o.orderId) 
                     From orders o
                     Group By o.product)