I will try to explain this with similar example. Lets suppose we want ALL Orders which their date match with the minimum for all the possible unique combinations of ShipCountry and ShipDate.
In your words, we want this:
- each row containing all fields.
- containing max n rows.
- Without rows with duplicate values in field ShipCity and ShipCountry.
- sorted on the OrderDate field.
This would be an approximate SQL for that request:
SELECT O.*
FROM Orders O
JOIN
(SELECT MIN(OrderDate) AS OrderDate FROM Orders O2
GROUP BY ShipCity, ShipCountry
) O2 ON O.OrderDate = O2.OrderDate
ORDER BY O.OrderDate
To do that, you could use DerivedTable and DynamicRelation. This is the C# code for that (using SelfServicing):
// First define the fields of the derived table select
ResultsetFields dtFields = new ResultsetFields(1);
dtFields.DefineField(OrderFields.OrderDate, 0, AggregateFunction.Min);
// now define the grouper that will be inside the devied table
var grouper = new GroupByCollection();
grouper.Add(OrderFields.ShipCountry);
grouper.Add(OrderFields.ShipCity);
// define the derived table
DerivedTableDefinition dtDefinition = new DerivedTableDefinition(
dtFields, "O2", null, grouper);
// specify the relation which is a dynamic relation.
var relation = new DynamicRelation(dtDefinition, JoinHint.Inner,
EntityType.OrderEntity, "O",
(new EntityField(OrderFieldIndex.OrderDate.ToString(), "O2", typeof(DateTime)) ==
OrderFields.OrderDate.SetObjectAlias("O")));
// add the dynamic relation to the relations that will be used in the main fetch
RelationCollection relations = new RelationCollection();
relations.Add(relation);
relations.SelectListAlias = "O"; // entities should target the aliased order table.
// sorter
var sorter = new SortExpression(OrderFields.OrderDate.SetObjectAlias("O") | SortOperator.Ascending);
// fetch the data
var maxRows = 10;
OrderCollection orders = new OrderCollection();
orders.GetMulti(null, maxRows, sorter, relations);
.. and this is the final generated SQL for above code:
SELECT DISTINCT TOP 10
[LPA_O2].[OrderID] AS [OrderId],
[LPA_O2].[CustomerID] AS [CustomerId],
[LPA_O2].[EmployeeID] AS [EmployeeId],
[LPA_O2].[OrderDate],
[LPA_O2].[RequiredDate],
[LPA_O2].[ShippedDate],
[LPA_O2].[ShipVia],
[LPA_O2].[Freight],
[LPA_O2].[ShipName],
[LPA_O2].[ShipAddress],
[LPA_O2].[ShipCity],
[LPA_O2].[ShipRegion],
[LPA_O2].[ShipPostalCode],
[LPA_O2].[ShipCountry]
FROM
(
( SELECT MIN([Northwind].[dbo].[Orders].[OrderDate]) AS [OrderDate]
FROM [Northwind].[dbo].[Orders]
GROUP BY [Northwind].[dbo].[Orders].[ShipCountry], [Northwind].[dbo].[Orders].[ShipCity]
) [LPA_O1]
INNER JOIN [Northwind].[dbo].[Orders] [LPA_O2] ON [LPA_O1].[OrderDate] = [LPA_O2].[OrderDate]
)
ORDER BY [LPA_O2].[OrderDate] ASC
... which is exactly what we want. Now try it with your own scenario. Buena suerte