Creating a column in a query that displays the row number

I want to add an additional column to a query that shows the row number of each row in the query.

In access I used a function in a macro as follows (adapted to Access from here https://www.experts-exchange.com/questions/20492559/Display-record-row-number-in-a-query.html):

Function GetRowNum(strQueryName As String, strIDField As String, varID As Variant) As Long
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [" & strIDField & "]" & " FROM " & strQueryName, dbOpenSnapshot)
Select Case rst(strIDField).Type
    Case dbBigInt, dbBinary, dbBoolean, dbByte, dbCurrency, dbDecimal, dbDouble, dbFloat, dbInteger, dbLong, dbSingle, dbVarBinary:
        rst.FindFirst "[" & strIDField & "]=" & varID
    Case dbChar, dbText, dbMemo:
        rst.FindFirst "[" & strIDField & "]='" & varID & "'"
    Case dbDate, dbTime:
        rst.FindFirst "[" & strIDField & "]=#" & Format(varID, "mm/dd/yyyy hh:nn:ss") & "#"
End Select

GetRowNum = rst.AbsolutePosition + 1

rst.Close
Set rst = Nothing
End Function

Then in the Query, I add a column with the following SQL:

GetRowNum("MyQuery","MyIDField",[MyIDField]) AS SortA

Where MyQuery is the name of the query you are calling the function from and MyIDField is the name of the primary key field.

How do I achieve this in Base? Is there a more straightforward method of adding a row number to a query?

Your first “problem” is Base not being a database, but a frontend to databases. Two embedded databases are usually available.
.
I just assume you use/test with the “default”, wich is/was most times HSQLDB V1 8 Sadly this Version included no window-functions like row_number().
.
For a suggestion see sql - HyperSQL partitioned row_number() equivalent - Stack Overflow

@Wanderer Thanks, half the challenge is me asking the right question.

I can see the SQL you have pointed me to could update a new column, but I cannot work out how to add this to a new column in my Query.

Points, please.

Thanks,

Do you want to get a number, which only count the rows and reflect this?

SELECT "a".*, 
(SELECT COUNT("ID") FROM "Table" WHERE "ID" <= "a"."ID") AS "RowNr" 
FROM "Table" AS "a" ORDER BY "ID"

Works, if “ID” is primary key. But will only be sorted right when sorted by primary key.

Changed the typo ORDERED BY. Has to be ORDER BY

I could not get the syntax to work in my query. My Query is named Query1. The table in my query is Table1

I seemed to have worked out there was a small typo in your snippet ‘ORDERED BY’ => ‘ORDER BY’ ?

The SQL is

SELECT Query1.*, ( SELECT COUNT( ID ) FROM Table1 WHERE ID <= Query1.ID ) RowNr FROM Table1 AS Query1 ORDER BY ID

Thanks.

Try applying this (HSQLDB):

Row_number() Over()

For example:

SELECT "First Name", Row_number() Over() FROM "Employess"

HSQL2. The embedded HSQL is documented here: Chapter 9. SQL Syntax with no Row_Number()
May be his Base document is connected to a spreadsheet or whatever. We still don’t know which software we are dicussing here.

@Villeroy , here is example (Query1). What am I doing wrong?
Birth.odb (11.9 KB)

Hurray, it’s an embedded Firebird. Now we know which software we are talking about.

1 Like

https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-windowfuncs-row-number