Question: Rank ordered by multiple columns executed by the server?

[Villason]

Hi,

How can I implement the RANK() function taking into account two columns for the ranking? The main column does not have unique values. This is the query:

select *, RANK() over (order by score, posteddate desc) as rank from Post

I need to implement pagination without the offset limit pattern and I thought a kind of ranking function would be ok. I have a partial implementation which only works with uniques, using the '>' or '<' operands on the key used for pagination.

Any idea? I cannot find a solution online.

Cheers.

[rowanmiller]

To do this you would need to drop down to a raw SQL query, using the DbSet.FromSql(string) method. You'd also need to introduce a new model type that includes the Rank property since it is in the SELECT list (we have #1862 tracking the ability to return results into a type that is not part of the model).

[divega]

@Villason If I understand your question correctly the other thing you may want to try is calling UseRowNumberForPaging() after UseSqlServer().