C#, 2.6, SelfServicing

Hi All,

I want to retrieve a collection using prefetch, but I only want to retrieve on record based on a certain foreign key field, sorted by date.

A simple example to illustrate it:

We have a Customer table, a Shop table and an Order table.

Each order has 2 FK fields: CustomerId and ShopId, it also has a Date.

What I would like to do is to prefetch the last Order for each Shop for the Customer

Therefore it should "distinct" on Order.ShopId.

How can I do this?

Thanks.

You can't fetch an entire record and specify distinct on one field. The distinct goes for the entire row, so the trick here is to use Group By.

Using the orders table of the Northwind database, having CustomerId and EmployeeId (similar to ShopId in your example).

The following returns the OrderId of the latest orders per each employee for the specified Customer.

SELECT MAX(OrderID) FROM Orders
WHERE CustomerID = 'HANAR'
Group By CustomerID, EmployeeId

Note: Since OrderId is an Identity column, the latest the Order the Bigger the OrderId, so we can ignore the date.

Removing the WHERE condition returns the same thing for all Customers

SELECT MAX(OrderID) FROM Orders
Group By CustomerID, EmployeeId

To return all the fields of the Orders, we can do the following:

SELECT * FROM Orders
WHERE OrderId IN
(
SELECT MAX(OrderID) FROM Orders
Group By CustomerID, EmployeeId
)

This can be accomplished using a FieldCompareSetPredicate When fetching the Orders.