[Solved] SQLite: INSERT only if price is different from last row

EverSQL Database Performance Knowledge Base

SQLite: INSERT only if price is different from last row

I have a SQLite simple table with 2 columns:

timestamp |last_price
1419873093|900.0
1419874283|900.0
1419940915|900.0
1419946324|916.58
1419946981|914.46
1419947981|800.0

I'd like to insert new values only if the price is different from last row price (I don't want consecutive prices to be the same). I can get the price from last row using:

SELECT last_price FROM BTC_BRL order by rowid desc limit 1;

Then I compare it with the new price, but if I could do this directly in the SQL statement, it would be better and faster.

I tried to use CASE WHEN, but without success.

Thanks!

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `BTC_BRL` ADD INDEX `btc_brl_idx_rowid` (`rowid`);
The optimized query:
SELECT
        BTC_BRL.last_price 
    FROM
        BTC_BRL 
    ORDER BY
        BTC_BRL.rowid DESC LIMIT 1

Related Articles



* original question posted on StackOverflow here.