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
Sql Query to Find Last Day of Current Month
Use a Like Statement on SQL Server Xml Datatype
How to Call Scalar Function in SQL Server 2008
If I Update a View, Will My Original Tables Get Updated
Sql Server - Return Schema for Sysobjects
How to Use a Trim Function in SQL Server
Efficiently Duplicate Some Rows in Postgresql Table
Using a View with No Primary Key with Entity
Rails Activerecord - How to Fetch Records Between Two Dates
How to Multiply All Values Within a Column with SQL Like Sum()
MySQL Correlated Subquery in Join Syntax
Blank Out Duplicate Column Values in SQL Reporting Services
Query to Convert from Datetime to Date MySQL
Any Disadvantages to Bit Flags in Database Columns
Optimising a Select Query That Runs Slow on Oracle Which Runs Quickly on SQL Server