how to get first/last record in a group by dynamic query using FetchTypedList

Posts   
 
    
csun
User
Posts: 3
Joined: 05-Dec-2007
# Posted on: 13-Mar-2008 22:38:21   

I am working on this problem: try to get the last offer for each item. here is a sample table (OfferTable)

ItemId offerId offerDate offerBy Price 1 1 1/1/2008 alice 10.00 1 2 1/2/2008 john 9.00 1 3 1/3/2008 tom 11.00 2 4 1/1/2008 alice 20.00 3 5 1/4/2008 bob 4.00 3 6 1/5/2008 tom 5.00

the result will be something like this:

itemId lastOfferId lastOfferDate OfferCount 1 3 1/3/2008 3 2 4 1/1/2008 1 3 6 1/5/2008 2

I know how to write it in SQL:

select * from ( select itemID, max(offerid) as lastoffer, count(*) as offercount from OfferTable group by itemID ) as x inner join OfferTable as o on o.itemId = x.itemID and o.offerid = x.lastoffer;

but I can use LLBLGen FetchTypeList() to generate dynamic query. can someone please help me out?

thanks a lot!!!!!

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 14-Mar-2008 09:09:03   

Try to re-write it as follows, which can be implemented by LLBLGen Pro using a FieldCompareSetPredicate

SELECT * FROM OfferTable
WHERE  OfferId IN 
(
SELECT max(OfferId) as OfferId FROM OfferTable 
GROUP BY itemID
)
csun
User
Posts: 3
Joined: 05-Dec-2007
# Posted on: 15-Mar-2008 00:36:27   

Thanks, man. that is quick!

yes. this is what I did. the problem is I also need offerCounter field.

in order to get both last offer and offer count, I query database twice, 1) get the ItemId and offerCounter, 2) query database again just like your did in your code, to get itemId and lastOffer info, 3) add a new datacolumn "OfferCount" in second resultTable, 4) update "OfferCount" value from first result table.

It is not very good to query database twice, especially with paging and sorting in the page. I really like to get all the data in one shot. right now, I disabled sort for OfferCount better solution?

supper thanks for your help!

kakaiya
User
Posts: 159
Joined: 20-Mar-2004
# Posted on: 22-Mar-2008 09:26:32   

Hi,

I have a similar SQL as above? Is there a way to get the required data using LLBLGen?

Regards,

goose avatar
goose
User
Posts: 392
Joined: 06-Aug-2007
# Posted on: 22-Mar-2008 19:21:32   

This is more a sql-query question than a LLBLGen question, at list from my point of view flushed there's no way to obtain that kind of data without using 2 query's. If it may exist, I'm sure there's a way to translate it to LLBLGen Code.