ROW_NUMBER function in FGDB SQL

[Bud]

It would help to have a ROW_NUMBER function in FDGB SQL.

The ROW_NUMBER() is a window function that assigns a sequential integer to each row of a query’s result set. Rows are ordered starting from one based on the order specified by the ORDER BY clause in the window definition.

https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/

Use cases:

1. Select greatest n (no ties), n being greater than 1
2. Select greatest n per group (no ties), n being greater than 1
3. Definition query to show every nth record plus the last record
4. Many other use cases. I use the ROW_NUM window function regularly in other databases.

[MErikReedAugusta]

I'm curious: Are you not able to pull the ObjectID field for this purpose?

[Bud]

@MErikReedAugusta 

The ObjectID is static. I want a dynamic row number within a sorted query.

Like this: Select the four largest cities. 

objectid in (
    select
        objectid
    from
        (select
            objectid,
            row_number() over (order by population_2021 desc) as rownum 
        from
            cities)
    where
        rownum <= 4)

This works in a mobile geodatabase. And there are enterprise geodatabase equivalents. But as far as I know, there isn't a function like ROW_NUMBER in file geodatabase SQL.

[Bud]

Or if window functions are too difficult to implement in FGDB SQL, then maybe a ROWNUM pseudo-column would work, similar to what Oracle does:

objectid in (
    select
        objectid
    from
        (select
            rownum, 
            objectid
        from
            cities
        order by
            population_2021 desc)
    where
        rownum <= 4)  

[Bud]

Related: 

• Add Join — Pseudo-ObjectID (row number) as unique ID field for 1:M join
• Disallow 1:M join layers as GP inputs instead of producing unexpected results

[Bud]

Calculate Field with sequential numbers, partition by GROUP_ID field, order by VALUES field

[Bud]

A ROW_NUM pseudo column would also help with Join — Control what related record gets used.

In a FGDB database view:

ORDER BY on the grouping ID/common ID field, then on the field that we want to prioritize for the one-to-first join, so that the desired row is at the top of each grouping. Reason: one-to-first joins use the related record that has the smallest object ID.
Replace the real OBJECTID field with a fake/new OBJECTID using ROW_NUM.