LINQ (to Oracle) - Row_Number() Over Partition By

Question

This is a possible duplicate of other Partition By + Rank questions but I found most of those questions/answers to be too specific to their particular business logic. What I'm looking for is a more general LINQ version of the following type of query:

SELECT id,
       field1,
       field2,
       ROW_NUMBER() OVER (PARTITION BY id
                          ORDER BY field1 desc) ROWNUM
FROM someTable;

A very common thing we do with this is to wrap it like in something like this:

SELECT id,
       field1,
       field2
FROM (SELECT id,
      field1,
      field2,
      ROW_NUMBER() OVER (PARTITION BY id
                         ORDER BY field1 desc) ROWNUM
      FROM someTable)
WHERE ROWNUM = 1;

Which returns the row containing the highest value in field1 for each id. Changing the order by to asc of course would return the lowest value or changing the rank to 2 will get the second highest/lowest value etc, etc. Is there a way to write a LINQ query that can be executed server side that gives us the same sort of functionality? Ideally, one that as performant as the above.

Edit: I've tried numerous different solutions after scouring the web and they all end up giving me the same problem that Reed's answer below does because the SQL generated includes an APPLY.

A couple examples I tried:

from p in db.someTable
group p by p.id into g
let mostRecent = g.OrderByDescending(o => o.field1).FirstOrDefault()
select new {
    g.Key,
    mostRecent
};

db.someTable
    .GroupBy(g => g.id, (a, b) => b.OrderByDescending(o => o.field1).Take(1))
    .SelectMany(m => m);

Both of these result in very similar, if not identical, SQL code which uses an OUTER APPLY that Oracle does not support.

To me, this seems to be a simple GROUP BY id and a MAX(field1). Did I miss something? — Francis P
– Francis P, Commented Mar 8, 2013 at 17:00
@FrancisP In it's current form, yes. But what if I didn't want the MAX and wanted the second or third instead? I have removed the emphasis on that particular part. — Kittoes0124
– Kittoes0124, Commented Mar 8, 2013 at 17:01
@Kittoes Same technique should work by adding a Skip call and then filtering out any empty results... — Reed Copsey
– Reed Copsey, Commented Mar 8, 2013 at 17:04

Reed Copsey · Accepted Answer · 2013-03-08 17:02:46Z

5

You should be able to do something like:

var results = someTable
                 .GroupBy(row => row.id)
                 .Select(group => group.OrderByDescending(r => r.id).First());

If you wanted the third highest value, you could do something like:

var results = someTable
                 .GroupBy(row => row.id)
                 .Select(group => group.OrderByDescending(r => r.id).Skip(2).FirstOrDefault())
                 .Where(r => r != null); // Remove the groups that don't have 3 items

answered Mar 8, 2013 at 17:02

Reed Copsey

567k80 gold badges1.2k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kittoes0124 Over a year ago

Now, I hate to be a pain in the ass about this but I'm just curious: what if I wanted the third OR most recent item if there is not a third?

Kittoes0124 Over a year ago

Tested your solution, it works in SQL Server but on Oracle the query that is generated will fail because Oracle does not support "APPLY". Any ideas?

philreed Over a year ago

@ReedCopsey Is there a way to structure this query to avoid the generated SQL using the APPLY function?

John Woo · Accepted Answer · 2013-03-08 17:05:14Z

1

an alternative way, by using a subquery which separately gets the maximum field1 for each ID.

SELECT  a.*
FROM    someTable a
        INNER JOIN
        (
            SELECT  id, max(field1) max_field
            FROM    sometable
            GROUP   BY id
        ) b     ON a.id = b.ID AND
                    a.field1 = b.max_field

when converted to LINQ:

from a in someTable
join b in 
    (
        from o in someTable
        group o by new {o.ID} into g
        select new 
        {
          g.Key.ID,
          max_field = g.Max(p => p.field1)
        }
    ) on new {a.ID, a.field1} equals new {b.ID, field1 = b.max_field}
select a

answered Mar 8, 2013 at 17:05

John Woo

265k70 gold badges509 silver badges500 bronze badges

Collectives™ on Stack Overflow

LINQ (to Oracle) - Row_Number() Over Partition By

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related