Return just one record for each value pair?

[Chris]

I have a simple Orders table that contains, among other things, customer
numbers, order numbers, and Item IDs:

CustNum, OrderNum, ItemID
12345, 08/22/2005, 546
12345, 08/22/2005, 548
12346, 08/22/2005, 602
12347, 08/22/2005, 502
12347, 08/22/2005, 503

As you can see, some orders have multiple items. I need to write a query
that will return one record for each customer/order: the record with the
lowest-numbered ItemID. Any additional records can be discarded.

Query Result:
12345, 08/22/2005, 546
12346, 08/22/2005, 602
12347, 08/22/2005, 502

I tried variations of the TOP 1 clause and the Unique Values and Unique
Records properties, but could not get the desired result. Any suggestions?
Thanks!

 

[Marshall Barton]

I have a simple Orders table that contains, among other things, customer
numbers, order numbers, and Item IDs:

CustNum, OrderNum, ItemID
12345, 08/22/2005, 546
12345, 08/22/2005, 548
12346, 08/22/2005, 602
12347, 08/22/2005, 502
12347, 08/22/2005, 503

As you can see, some orders have multiple items. I need to write a query
that will return one record for each customer/order: the record with the
lowest-numbered ItemID. Any additional records can be discarded.

Query Result:
12345, 08/22/2005, 546
12346, 08/22/2005, 602
12347, 08/22/2005, 502

That's an aggragate kind of problem:

SELECT CustNum, OrderNum, Min(ItemID)
FROM Orders
GROUP BY CustNum, OrderNum

 

[Marshall Barton]

I have a simple Orders table that contains, among other things, customer
numbers, order numbers, and Item IDs:

CustNum, OrderNum, ItemID
12345, 08/22/2005, 546
12345, 08/22/2005, 548
12346, 08/22/2005, 602
12347, 08/22/2005, 502
12347, 08/22/2005, 503

As you can see, some orders have multiple items. I need to write a query
that will return one record for each customer/order: the record with the
lowest-numbered ItemID. Any additional records can be discarded.

Query Result:
12345, 08/22/2005, 546
12346, 08/22/2005, 602
12347, 08/22/2005, 502

I just realized that you want to get all the fields in the
desired records.

SELECT T.*
FROM Orders As T INNER JOIN
[SELECT M.CustNum, M.OrderNum, Min(M.ItemID) As MinID
FROM Orders As M
GROUP BY M.CustNum, M.OrderNum]. As X
ON T.CustNum = X.CustNum
AND T.OrderNum = X.OrderNum
AND T.ItemID = X.ItemID

Alternatively:

SELECT T.*
FROM Orders As T
WHERE T.ItemID =
(SELECT Min(X.ItemID)
FROM Orders As X
WHERE T.CustNum = X.CustNum
AND T.OrderNum = X.OrderNum)

 

[Chris]

Thanks, Marsh! That worked great.