I have a simple query which returns the record_id of the most recent record for each user_id as follows and would appreciate it if someone could outline a corresponding QuerySpec definition

SELECT max(record_id) FROM sometable y JOIN (SELECT [user_id], MAX([orig_date]) max_date from sometable GROUP BY [user_id]) x ON (x.user_id = y.user_id and x.max_date = y.orig_date) group by y.user_id, y.orig_date

Hi cwb,

Here is an example similar to yours. Lets say I want this query (Get all orders with a total higher than a given limit):

SELECT O.*
FROM 
(
    SELECT OrderID, SUM(Quantity * UnitPrice) As Total
    FROM [Order Details]
    Group By OrderID
) As OrderDetailTotals 
    INNER JOIN Orders O On OrderDetailTotals.OrderID = O.OrderID
WHERE OrderDetailTotals.Total > @limit

This will be the code for QuerySpec using Adapter :

var qf = new QueryFactory();
var q = qf.Order
    .From(QueryTarget
        .InnerJoin(qf.Create("ods")
                .Select(OrderDetailFields.OrderId,
                       (OrderDetailFields.Quantity * OrderDetailFields.UnitPrice)
                        .Sum().As("Total"))
                .GroupBy(OrderDetailFields.OrderId))
        .On(OrderFields.OrderId == qf.Field("OrderId").Source("ods")))
    .Where(qf.Field("Total").Source("ods").GreaterThan(5000));

var orders = adapter.FetchQuery(q);

This is a concept called DerivedTableDefinition. Please read the documentation to know more about how to get it working with QuerySpec, LLBLGen API or Linq2LLBL.

Dear daelmo,

Thanks for your help.