detect where is a rowid in a sql query ?

Databases
1

Hi everyone,
is there a way, in an sql request, to know where is a rowid inside a query ?

I have one rowID I want to retrieve the offset
what query can I add to my original sql query to get the offset of that particular rowid ?

thanks.

2

do not equate rowid to a physical location in the file.
RowID values are not required to be in physical sequencial order, not are they required to be consecutive

3

it is completely temporary.
after a select query, I display the result in a listbox, and I want to be able to go to a rowid specified record.
here rowid means a unique value (which it always is)
it’s not a misuse of rowid …

4

Are you selecting a subset of an existing table directly to a recordset to feed your listbox?
or are you creating a new table with the results and using that to feed the listbox?

If the first, you will have disjointed rowids, as they belong to the source table, and not recalculated by the query
if the second, then a new set of rowids are created for the new table.

5

I am selecting a subset of a table to a recordset (using eventually LIMIT and OFFSET) to feed the listbox.
I want to scroll the listbox to a given rowid, that I know is in the subset I just created, but want to know where in the listbox.
as the database can be remote, I want if possible to calculate that listbox offset in the sql query to be as fast as possible
not loop sequentialy through all the local listbox to get the rowid I search.

6

basically no… assume for example you inital listbox record is rowid 423, and you want to offset by 45
there is nothing to say that rowid 468 is even in your result set (or for that matter rowid 468 is really 45 records away)

A rowid should NEVER be used to access any record other than the record to which the rowID belongs… there are zero guarantee that a record with a “derived” rowid exists, or exists in the context that the method of derivation assumed.

ORDER BY, INSERT, DELETE , VACUUM all can affect the content or distribution of “rowid” values in a recordset

7

I make a sql query.
I get a recordset with rowids (125,128,654,3258,14)
they are ordered and selected by the query.
I want to know that the rowid 654 of THAT particular query I just did is in the 3rd position.
and I want to get this result in the precise select query I just did
I don’t want to iterate through the recordset because it can be too long for a remote database.

the idea is to edit a particular record knowing its rowid (that’s a good use of rowid !)
I want to scroll to the selection in the lisbox, select the row with that rowid, and edit it.

8

so make a dictionary as you load the listbox

9

I don’t load all the records into the listbox, only the visible part (using limit and offset in the query)
there are tables with 1 million records, and on a remote server…
that’s why I want to get that offset from the sql query, and then get the recordsets of only the visible rows around the one I want to edit.

10

well good luck

11

Store the RowID in the listbox RowTag. When the user wants to update a particular listbox row, grab the rowid, read the record onto a fresh recordset, and update it. You get the advantage of not having stale data in the recordset. Since you are reading and updating a single record in response to user action, performance is not an issue.

12

already done like that.
the problem is I only have a small part of the whole recordset in the local listbox.
I want to ask the remote server where would be the given rowid position in the complete recordset.

13

You’re using PostgreSQL, right? If so, take a deep dive into the so called window-functions and the “row-number”-function. See http://www.postgresqltutorial.com/postgresql-row_number/ .

14

didn’t know of this row-number function. nice ! thanks.

but I was thinking of using array_agg postgres function.
select array_agg( myrowidfield) from mytable where … order by …

how can I get the position in the array of a given rowid, and return that in the select only, without the whole array that can be very big ?

15

Not sure why you would want to use the array_agg function, if you don’t want the array. If you just want the position of a particular record within a query something along the lines of

SELECT ROW_NUMBER () OVER (ORDER BY somefieldToOrderBy)
FROM mytable where myrowidfield=myID;

should get you what you want. I haven’t tested any of this, you might have to tweak this. mytable in turn can also be a complicated view or a function.

16

in xojo, I would write myRowIDsArray.IndexOf( theRowIDIWant) to get what I need
what do I have to use for postgres and the array I get with array_Agg( myrowidfield) ?

17

I can use the array on the server, but I dont want to download it localy in a recordset because it can be too big for that.
on the server side, it will stay in acceptable time.
the only thing I need is the position of the rowid I need in the query result.

18

For finding an element in array see https://stackoverflow.com/questions/8798055/finding-the-position-of-a-value-in-postgresql-arrays . But I would tackle your task differently. Here is an example, it will tell you what position a person name ‘Jordanov’ is in my person table if it is ordered by name. I create a view first to make subsequent queries easier:

create view vw_myView as select lastname,row_number() over(order by lastname) as rankbyalphabeticorder from person;
select rankbyalphabeticorder from vw_myView where lastname=‘Jordanov’;

row_number

     62

(1 row)

It looks to me you’re trying to avoid these window functions for no good reason.

19

I was able to do it finally with array_agg and the intarray extension.
but your solution is better it doesnt need any extension nor any group by to achieve the same result.
thanks.
now I’m able to find and edit one record among a 900k records listbox on a remote database in less than 90ms.