idea ROW_NUMBER function in FGDB SQL in ArcGIS Pro Ideas

ROW_NUMBER function in FGDB SQL

Bud — Tue, 06 Aug 2024 20:21:44 GMT

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:

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

Re: ROW_NUMBER function in FGDB SQL

MErikReedAugusta — Wed, 17 Jan 2024 16:34:00 GMT

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

Re: ROW_NUMBER function in FGDB SQL

Bud — Thu, 18 Jan 2024 05:49:45 GMT

@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.

Re: ROW_NUMBER function in FGDB SQL

Bud — Thu, 18 Jan 2024 05:52:08 GMT

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)

Re: ROW_NUMBER function in FGDB SQL

Bud — Mon, 15 Jul 2024 04:39:33 GMT

Re: ROW_NUMBER function in FGDB SQL

Bud — Thu, 22 Aug 2024 18:12:45 GMT

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

Re: ROW_NUMBER function in FGDB SQL

Bud — Sat, 24 Aug 2024 00:27:37 GMT

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.