Rank over implementation

Posts   
 
    
nweinit
User
Posts: 21
Joined: 16-Nov-2016
# Posted on: 09-Sep-2017 00:09:29   

How would I go about querying with a rank over using an adapter? Currently I'm applying the rank after fetching the entity collection, but that is not efficient at all since the fetching returns 1000 times more data than I need (which take significant longer time to process). Using OracleODPNet and LLBLGen 5.1.5

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 09-Sep-2017 06:38:20   

Hi nweinit,

This isn't a built-in supported construct. I coded a RANK possibility extension to the DataAccessAdapter class for FullTextSearch (SQLServer) some time ago: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=20686&StartAtMessage=0&#116580

However that was for SQLServer and for LLBLGen v2.6. But the idea of extending the classes is similar.

There are various ways to use Oracle's RANK. Please show us a use case of RANK OVER so we can get the idea of what you want.

How would you want to control when to use it RANK and when not?

David Elizondo | LLBLGen Support Team
nweinit
User
Posts: 21
Joined: 16-Nov-2016
# Posted on: 11-Sep-2017 16:04:16   

Thanks for the response daelmo. What i'm trying to achieve is basically group my parent table records and get the max/latest out of each group so that I could fetch the appropriate child records. As you can understand the DB structure is built with parent and child tables where many records in the child table have the FK to a single record in the parent table. I don't want to get into too complication here, if there's no built-in support for that directly in LLBLGen then I think i'll just default to a simple query getting the parent records first and then passing those keys to the child table entity collection fetch statement (unless you can tell me this behavior is actually easy/supported)

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 11-Sep-2017 21:18:11   

What i'm trying to achieve is basically group my parent table records and get the max/latest out of each group so that I could fetch the appropriate child records.

Since you are looking for the max/latest, you can simply order by that specific field and select Top 1.

i'll just default to a simple query getting the parent records first and then passing those keys to the child table entity collection fetch statement (unless you can tell me this behavior is actually easy/supported)

You can use PrefetchPath, please check the link for more details and code samples.