We have an app where families apply to a bunch of schools and rank order their preferences (since they're typically more than one). If they don't change the rank order, the value of that field (

prefrank

) is the placement of each school they chose by geographic distance, i.e. if you applied to the 2nd, 4th, and 5th closest schools, your prefranks are 2, 4, and 5. We want a field that is just the 'sort order' -- that is, if you chose 2, 4, and 5, we want a field whose value is 1, 2, and 3. Obviously we can manually set and track this any time it changes, but I feel like there is probably also some SQL we could use here to compute a value that says 'the value of

prefRankSort

is equal to the row number of this row in the table for this primary key when sorted by

prefRank

.' yes? and would that be super expensive to compute?

Would something like this do it?

ROW_NUMBER() OVER(PARTITION BY prefRank) AS prefRankSort

If it's per family,

PARTITION BY familyId ORDER BY prefRank

I know I can do that in a query, what I don't know is if I can make a field whose computed value is that value

I don't think window functions are supported for computed columns

And it'd probably be pretty expensive to include that in a view

pretty much live on using

row_number()

for pagination/other fun sorting in views and the database isnt dead yet... famous last words