"SELECT max(rowid) from studdetail" this should give maximum row in the table. And it is giving at one place but at another it is giving n+1(n is the no. of rows). What can be the reasons? I read something like hidden row is it that if yes then pls explain. Thanks
Asked
Active
Viewed 228 times
-2
-
1What does this mean? `And it is giving at one place but at another` – SS_DBA Mar 25 '19 at 16:31
-
Which database? You have this tagged for multiple ones? I assume sqlite thanks to the use of the rowid column? – Shawn Mar 25 '19 at 16:33
-
*""SELECT max(rowid) from studdetail" this should give maximum row in the table"* It also assumes you never delete the "last" `(max(rowid))` record from the table.. Also you are talking about SQLite because you are talking about rowid? – Raymond Nijland Mar 25 '19 at 16:45
-
Oh, wait. Are you expecting the max rowid to equal the number of rows in the table? That's not how things work. – Shawn Mar 25 '19 at 18:18
1 Answers
1
max(rowid) will return the highest rowid.
If rows have been deleted, or that rowid's have been skipped (can occur), or the rowid has been manually set, the highest allocated rowid may be greater than the number of actual rows. That is the rowid value for a row does not need to exactly represent it's position.
count(rowid) would return the number of rows.
Example
Consider the following :-
DROP TABLE IF EXISTS thetable;
CREATE TABLE IF NOT EXISTS thetable (mycolumn);
INSERT INTO thetable VALUES('A'),('B'),('C'),('D'),('E');
SELECT max(rowid), count(rowid) FROM thetable;
DELETE FROM thetable WHERE mycolumn = 'C';
SELECT max(rowid), count(rowid) FROM thetable;
INSERT INTO thetable (rowid,mycolumn) VALUES(26,'Z');
SELECT max(rowid), count(rowid) FROM thetable;
The first result, both max(rowid) and count(rowid) are 5 :-
The second result, as an intermediate row has been deleted, shows a discrepancy between the max(rowid) (5) and count(rowid) (4) :-
The third result, as a rowid value has been forced to be 26, shows an even greater discrepancy (26 v 5) :-
