CODEWITHSUNDEEP
sqlsqlite
alancc
alancc

Reputation: 799

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

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

Upvotes: 0

Views: 759

Answers (2)

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74605

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

Upvotes: 1

Related Questions