SQLite Studio query works different when I make it a subquery

Question

I'm using the Superstore_test dataset here: https://www.kaggle.com/blurredmachine/superstore-time-series-dataset

I want to make a column that shows the sales of the next row down. I put this query in and I get very odd results where some rows are matched to the sales of the next row, and some aren't. The weird thing is that if I take the subquery and use it by itself, it returns a table with next_sales column lined-up perfectly. Can someone please explain this conceptually?

Query:

UPDATE Superstore_test
SET next_sales = subquery.next_sales
FROM (
  SELECT order_id, sales, LEAD(sales) OVER (ORDER BY order_id asc) AS next_sales
  FROM Superstore_test
  ) AS subquery
WHERE subquery.sales = Superstore_test.sales
;

I want to make a new column that shows the sales of the next row down. I get this though:

sales	next_sales
299.94	25.76
25.76	52.59
271.9	45.84
45.84	9.82
9.82	998.82
93.36	66.97600000000001
541.24	106.32
106.32	1323.8999999999999
1323.8999999999999	299.94

How have you determined that `next_sale` is incorrect? My first guess is that your table, which is unordered by definition, is just not in the order you would like it to be in, AND you have no way of telling if that's the case because you don't have `order_id` in the table. — JNevill, Apr 28 '23 at 18:47
Furthermore joining on `sales` seems super sus (is `order_id` not in your `Superstore_test` table? And if not: why not?). At any rate, if you want to maintain ordering, then make sure the column by which you order is present in all of your result sets and tables so you can issue an `ORDER BY` clause in your SQL, otherwise you'll just get whatever random order the database burps up the records in. — JNevill, Apr 28 '23 at 18:48

score 1 · Answer 1 · edited May 04 '23 at 10:20

1

I figured it out, for some reason, when I order by ROWID(), it works.

UPDATE Superstore_test
SET next_sales = subquery.next_sales
FROM (
  SELECT order_id, sales, LEAD(sales) OVER (ORDER BY ROWID) AS next_sales
  FROM Superstore_test
  ) AS subquery
WHERE subquery.sales = Superstore_test.sales
;

edited May 04 '23 at 10:20

Phil Dukhov

67,741
15
184
220

answered Apr 28 '23 at 20:39

Tynan

9
1

Then why did you say that *if I take the subquery and use it by itself, it returns a table with next_sales column lined-up perfectly* by using ORDER BY order_id? – forpas Apr 28 '23 at 20:41
Please look at the formatted version of a post before publishing. See the edit help re code block formats. Please edit you question per feedback, posts are here forever for other people. – philipxy Apr 28 '23 at 20:45
Tables have no row order, result sets have row order per an outermost order by & a window function order by meaningful columns gives meaningful values to row_number. This code does not work. – philipxy Apr 28 '23 at 20:54

SQLite Studio query works different when I make it a subquery

1 Answers1