I have a situation where i have the following

productline id (pk)

product id (pk) productlineid name

latestoffer id (pk) OfferedToUserId productid offerdate price

User id (pk) Name

I need to grab the latest offer from the database quicker than browsing though the product line/product/lastoffer to find the best, it\s not too efficient to grab the data. But if i could get my hands on latestoffer entity collection (best per price and ueserid for any point in time)

to do this with sql i would try the following:

select * from latestoffer inner join product on latestoffer.productid = product.id

where   latestoffer.id in 
        (select max(latestoffer.id) 
            from latestoffer inner join product on product.id = latestoffer.productid
            where latestoffer.offerdate <= '02/25/1994 00:00'
            group by latestoffer.productid, OfferedToUserId)
    and productlineid = 142

I need to run the query with the getmulti within selfservicing. Problem that i'm having is mainly how to represent the group by statement within the subquery.

This is a quick translation of the query to a similar situation, so sql might not be rock solid. Hopefully you can see what i'm attempting.

I couldn't get what this query should achieve, specially the inner query!!

Have you tried this query in the query analyzer?

Would you please post a data sample from your tables and of the result set you want to be returned?

As a general rule, use the FieldCompareSetPredicate for the SubQuery (IN) Please refer to the LLBLGen Pro documentation "Use the generated code -> SelfServicing -> The predicate system"

And use the following overload for the groupby

public FieldCompareSetPredicate(
   IEntityField field,
   IEntityField setField,
   SetOperator operatorToUse,
   IPredicate filter,
   IRelationCollection relations,
   string objectAlias,
   long maxNumberOfItemsToReturn,
   ISortExpression sorter,
   bool negate,
   IGroupByCollection groupByClause
);

Thanks for the reply. the suggestion you gave did the trick in my case. I didnt realize that group by existed within the FieldCompareSetPredicate method.

steveh wrote:

Thanks for the reply. the suggestion you gave did the trick in my case. I didnt realize that group by existed within the FieldCompareSetPredicate method.

It was added in v1.0.2005.1