1

I'm trying to get the absolute record number of a row after it has been returned by a query (in Perl). Let's say I query my table as follows:

my $stmt = "SELECT * FROM MAIN WHERE item='widget' LIMIT 1;";
my $sth  = $dbh->prepare( $stmt );
my $rv   = $sth->execute();

How can I find out the absolute row number of the returned row? I tried using ROWID:

my $stmt = "SELECT ROWID, * FROM MAIN WHERE item='widget' ORDER BY ROWID LIMIT 1;";
my $sth  = $dbh->prepare( $stmt );
my $rv   = $sth->execute();
my $row  = $sth->fetchrow_hashref();

if( defined( $row ) ) {
  $RecordNumber = $row->{'rowid'};
}

This works if you never ever delete a row from the table.

But Row IDs never change. So if you delete rows from the table, the rowids no longer match the absolute row number in the table.

I hope my question is clear (English is not my native language).

Zippy1970
  • 601
  • 6
  • 20
  • http://stackoverflow.com/questions/9151706/getting-row-number-for-query http://stackoverflow.com/questions/19479253/how-to-get-row-number-in-sql-lite – Matt Jacob Dec 08 '15 at 23:42
  • Yes, I saw those answers but my knowledge of SQLite is too limited to understand why that's actually the answer to my question. I'll go over them again. Thanks! – Zippy1970 Dec 08 '15 at 23:45
  • What do you mean by "absolute row number"? You just want the result set ordered from `1..n`? What are you trying to do? – Matt Jacob Dec 08 '15 at 23:47
  • I'm trying to create an old dBase style interface to an SQLite Database. In dBase, each record had a record number. The first record was record #1, the second record #2, etc. If you deleted the 2nd record, what used to be record #3 became record #2. Record #4 became #3, etc. If you did a locate, it would return this record number. The record number is the absolute position of the record in the database. It's not some unique ID because it changes as the database changed. I'm trying to emulate this behavior. So if I do a query I want to know what the position of the returned row in the table is. – Zippy1970 Dec 08 '15 at 23:52

1 Answers1

1

Let's take this example

sqlite> create table my_example (field1);
sqlite> insert into my_example values ('abc');
sqlite> insert into my_example values ('booooo');
sqlite> insert into my_example values ('3231-556');
sqlite> .mode column
sqlite> .header on
sqlite> select * from my_example;
field1
----------
abc
booooo
3231-556
sqlite> select rowid, * from my_example;
rowid       field1
----------  ----------
1           abc
2           booooo
3           3231-556
sqlite> delete from my_example where field1='abc';
sqlite> select rowid, * from my_example;
rowid       field1
----------  ----------
2           booooo
3           3231-556
sqlite> insert into my_example values ('abc');
sqlite> select rowid, * from my_example;
rowid       field1
----------  ----------
2           booooo
3           3231-556
4           abc
sqlite>

a way of getting what I understand you want to achieve (something similar to a rown_num) would be:

sqlite> select (select count(*) from my_example b where a.rowid >= b.oid ) as cnt, * from my_example a;
cnt         field1
----------  ----------
1           booooo
2           3231-556
3           abc
sqlite>

hope this helps

Roberto
  • 195
  • 1
  • 6