How to know the current row is the last one in SQlite?

Question

I am using Sqlite 3.

I need to know whether the current row is the last one in a SQL query result, before invoking the sqlite3_step().

After checking the existing post

SQLite3 - how to know if the current row is the last row

and

How to check if the current row is the last selected row in a sql query?

Since each row in SQlite3 has a ROWID, I figure out a way to write the code, as follows:

SELECT (ROWID = MAX(ROWID)) AS IsLast FROM MyTable ORDER BY ROWID;

The data in MyTable is:

|BID|
|3|
|2|
|5|
|7|

The expected result is all rows except the last one is FALSE, as below:

    |0|
    |0|
    |0|
    |1|

But the actual result is:

|1|

Moreover, I try to integrate the code into a more complex query, which comes from SQL select only rows with max value on a column as below:

SELECT a.id, a.rev, a.contents, b.mycount, (a.ROWID = MAX(a.ROWID)) AS IsLast
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev, COUNT(id) mycount
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev ORDER BY a.ROWID;

Take the original table in the original post as an example:

        +------+-------+--------------------------------------+
        | id   | rev   | content                              |
        +------+-------+--------------------------------------+
        | 1    | 1     | ...                                  |
        | 2    | 1     | ...                                  |
        | 1    | 2     | ...                                  |
        | 1    | 3     | ...                                  |
        +------+-------+--------------------------------------+

After using the query, the expected result should be(the last column is IsLast flag):

    |2|1|...|1|0|
    |1|3|...|3|1|

But the actual result is only one row:

|1|3|...|3|1|

What is the problem?

Thanks

AFAIK, You can use [`rownumber() over (order by rowid desc)`](https://stackoverflow.com/q/16847574/4519059) instead, and when it returns `1` you now it's max `rowid` -HTH ;). — shA.t, Dec 09 '18 at 05:13

D-Shih · Accepted Answer · 2018-12-09T05:42:23.597

The error had caused by your subquery didn't contain ROWID column but you use this column in the main query.

You can try to use a subquery to get MAX(ROWID) then use CASE WHEN to set the last flag.

Your first query

Schema (SQLite v3.18)

CREATE TABLE YourTable(
  BID int
);

INSERT INTO YourTable VALUES (3);
INSERT INTO YourTable VALUES (2);
INSERT INTO YourTable VALUES (5);
INSERT INTO YourTable VALUES (7);

Query #1

SELECT  
     BID,(CASE WHEN (SELECT MAX(ROWID) FROM YourTable) = a.ROWID then 1 else 0 end) AS IsLast
FROM YourTable a;

| BID | IsLast |
| --- | ------ |
| 3   | 0      |
| 2   | 0      |
| 5   | 0      |
| 7   | 1      |

View on DB Fiddle

Schema (SQLite v3.18)

CREATE TABLE YourTable(
  Id int,
  rev int,
  contents varchar(50)
);

INSERT INTO YourTable VALUES (1,1,'test1');
INSERT INTO YourTable VALUES (2,1,'test2');
INSERT INTO YourTable VALUES (1,2,'test1');
INSERT INTO YourTable VALUES (1,3,'test2');

Query #1

SELECT a.*,
       (CASE WHEN maxROWID = a.ROWID then 1 else 0 end) AS IsLast
FROM YourTable a
 JOIN 
(
  SELECT Id,
         MAX(rev) rev, 
         COUNT(id) mycount,
         (SELECT MAX(ROWID) FROM YourTable) maxROWID
  FROM YourTable
  group by Id
) b  ON a.id = b.id AND a.rev = b.rev
ORDER BY a.ROWID;

| Id  | rev | contents | IsLast |
| --- | --- | -------- | ------ |
| 2   | 1   | test2    | 0      |
| 1   | 3   | test2    | 1      |

View on DB Fiddle

@alancc I saw you edit your question I edit my answer you can try it. — D-Shih, Dec 09 '18 at 05:37
For query1, it works. However, when I try to simply it to: SELECT BID,(CASE WHEN MAX(ROWID) = a.ROWID then 1 else 0 end) AS IsLast FROM YourTable a; Then I will fail again. I cannot understand why I must use a subquery to get the max value of ROWID? — alancc, Dec 09 '18 at 05:49
For query 2, the (SELECT MAX(ROWID) FROM YourTable) maxROWID may returns a ROWID whose corresponding row may not in the final result set, thus cause all IsLast becomes 0? — alancc, Dec 09 '18 at 05:54
@alancc The subquery can get the `MAX` of id in your table then make compare with it to check last row — D-Shih, Dec 09 '18 at 06:08

score 1 · Answer 2 · answered Dec 09 '18 at 04:35

1

The documentation says sqlite3_step() returns different values depending on the state of the query

https://www.sqlite.org/c3ref/step.html

At first glance it looks like you should call it until it returns SQLITE_DONE. Then, you'll need to reset it before it can be called again on that statement

answered Dec 09 '18 at 04:35

Caius Jard

72,509
5
49
80

Yes, but when I get SQLITE_ROW instead of SQLITE_DONE, I don't know whether there is a next row until I invoke sqlite3_step again and get SQLITE_DONE. I want to know before doing so. – alancc Dec 09 '18 at 05:11
1

Why? Just invoke it and get the DONE, then stop. If you need access to the previous row at that moment, keep a reference to the previous row data as you loop. I.e. When you get the DONE, you'll suddenly know the most recently read data will have been the last row, so arrange your code so it doesn't overwrite/lose it – Caius Jard Dec 09 '18 at 05:18

How to know the current row is the last one in SQlite?

2 Answers2