Search code examples
mysqlsqliterowid

MAX(rowid) giving n+1 rows as answer

"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

Solution

  • 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 :-

    enter image description here

    The second result, as an intermediate row has been deleted, shows a discrepancy between the max(rowid) (5) and count(rowid) (4) :-

    enter image description here

    The third result, as a rowid value has been forced to be 26, shows an even greater discrepancy (26 v 5) :-

    enter image description here