Retrieving groups of data limited by total number of records accross the groups

I have the following problem to solve. I need to prepare write a sql query that processes groups of data in batches.

Suppose I have the following simple data model: university has many (one-to-many) students:

Un1 -> (St_1, St_2, St_3)

Un2 -> (St_4, St_5)

Un3 -> (St_6, St_7, St_8)

The question is that I need to process first univiersities (ordered by name) whose total number of students does not exceed a given batch_size, or just the first university.

So, if my batch_size is 6, the query is supposed to retrieve the first two universities:

Un1 -> (St_1, St_2, St_3)

Un2 -> (St_4, St_5)

If batch_size is 1, the result should be:
Un1 -> (St_1, St_2, St_3)

I’m using Oracle, Nhibernate and Linq to Nhibernate. It would be cool if I could manage to do this using LINQ.


Source: oracle

Leave a Reply