nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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:

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.

Vind meer berichten getagd met

Geodatabase

Status: Openen

Reacties

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

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.

Doe mee aan het gesprek

Meld je aan om te posten, content te volgen en meer. Nieuw hier? Registreer gratis.