Create a Sqlite View Where a Row Depends on The Previous Row

Is there a way to access a value of the previous row in a trigger with sqlite?

To get the new value from a different row, you must use a correlated subquery:

    UPDATE ops SET account_balance=
(CASE WHEN idx=1
THEN op_sum
ELSE op_sum + (SELECT account_balance
FROM ops AS previous_ops
WHERE previous_ops.idx = ops.idx - 1)
END);

Is there a way to access the previous row value in a SELECT statement?

SQL has no built in notion of order, so you need to order by some column for this to be meaningful. Something like this:

select t1.value - t2.value from table t1, table t2 
where t1.primaryKey = t2.primaryKey - 1

If you know how to order things but not how to get the previous value given the current one (EG, you want to order alphabetically) then I don't know of a way to do that in standard SQL, but most SQL implementations will have extensions to do it.

Here is a way for SQL server that works if you can order rows such that each one is distinct:

select  rank() OVER (ORDER BY id) as 'Rank', value into temp1 from t

select t1.value - t2.value from temp1 t1, temp1 t2
where t1.Rank = t2.Rank - 1

drop table temp1

If you need to break ties, you can add as many columns as necessary to the ORDER BY.

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

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

Get previous and next row from rows selected with (WHERE) conditions

you didn't specify your DBMS, so the following is ANSI SQL:

select prev_word, word, next_word
from (
select id,
lag(word) over (order by id) as prev_word,
word,
lead(word) over (order by id) as next_word
from words
) as t
where word = 'name';

SQLFiddle: http://sqlfiddle.com/#!12/7639e/1

How do i select the value of a certain column from the last row in sqlite3 and python?

This depends on your definition of last row. If you want to select the row you just inserted, you can use cursor.lastrowid.

c.execute("SELECT stare FROM unu WHERE ROWID=(?)", (c.lastrowid,))

If you want the last row anyone inserted (someone may have inserted a row between the insert and select statements), then you need a PK with AUTOINCREMENT (just "id INTEGER PRIMARY KEY" will make it a copy of the ROWID which is not guaranteed to be in crescent order):

c.execute("SELECT stare FROM unu ORDER BY id DESC LIMIT 1")

How to get previous row value

You would have to join the table with itself, I'm not sure if this is 100% legitimate SQL, but I have no SQL-Server to try this at the moment, but try this:

SELECT (ID, Value) from table as table1
inner join table as table2
on table1.ID = (table2.ID -1)

Increase the value of a specific row depending on the input of another table using triggers SQlite

I assume that the column ID in the table B is the primary key.

If the table B does not contain a row for each possible length, you can use UPSERT inside the trigger, so that if a word with a new length is inserted in A then a new row with value = 1 will be inserted in B, else the existing row will be incremented:

CREATE TRIGGER update_value AFTER INSERT ON A
BEGIN
INSERT INTO B(id, value)
SELECT LENGTH(NEW.word), 1
ON CONFLICT(id) DO UPDATE
SET value = value + 1;
END

See the demo.

sql pulling a row for next or previous row of a current row

This is what I use for finding previous/next records. Any column in your table can be used as the sort column, and no joins or nasty hacks are required:

Next record (date greater than current record):

SELECT id, title, MIN(created) AS created_date
FROM photo
WHERE created >
(SELECT created FROM photo WHERE id = '32kJ')
GROUP BY created
ORDER BY created ASC
LIMIT 1;

Previous record (date less than current record):

SELECT id, title, MAX(created) AS created_date
FROM photo
WHERE created <
(SELECT created FROM photo WHERE id = '32kJ')
GROUP BY created
ORDER BY created DESC
LIMIT 1;

Example:

CREATE TABLE `photo` (
`id` VARCHAR(5) NOT NULL,
`title` VARCHAR(255) NOT NULL,
`created` DATETIME NOT NULL,
INDEX `created` (`created` ASC),
PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('XEi43', 'my family', '2009-08-04');
INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('dDls', 'friends group', '2009-08-05');
INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('32kJ', 'beautiful place', '2009-08-06');
INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('EOIk', 'working late', '2009-08-07');

SELECT * FROM photo ORDER BY created;
+-------+-----------------+---------------------+
| id | title | created |
+-------+-----------------+---------------------+
| XEi43 | my family | 2009-08-04 00:00:00 |
| dDls | friends group | 2009-08-05 00:00:00 |
| 32kJ | beautiful place | 2009-08-06 00:00:00 |
| EOIk | working late | 2009-08-07 00:00:00 |
+-------+-----------------+---------------------+

SELECT id, title, MIN(created) AS next_date
FROM photo
WHERE created >
(SELECT created FROM photo WHERE id = '32kJ')
GROUP BY created
ORDER BY created ASC
LIMIT 1;

+------+--------------+---------------------+
| id | title | next_date |
+------+--------------+---------------------+
| EOIk | working late | 2009-08-07 00:00:00 |
+------+--------------+---------------------+

SELECT id, title, MAX(created) AS prev_date
FROM photo
WHERE created <
(SELECT created FROM photo WHERE id = '32kJ')
GROUP BY created
ORDER BY created DESC
LIMIT 1;

+------+---------------+---------------------+
| id | title | prev_date |
+------+---------------+---------------------+
| dDls | friends group | 2009-08-05 00:00:00 |
+------+---------------+---------------------+


Related Topics



Leave a reply



Submit