0 like 0 dislike
27 views
table `nspcl`

----------
id | partnum
----------
1 | 7079626
2 | 7079623
3 | 7079593
4 | 7079552
.....

we need to calculate the difference between the nearest end, that is
7079552 - 7079593 = -41
7079593 - 7079623 = -30
7079623 - 7079626 = -3

is it possible to implement this mysql query?
| 27 views

0 like 0 dislike
You can use window functions

``SELECT partnum, LAG(partnum) OVER w AS 'prev_partnum', partnum - LAG(partnum) OVER w AS 'diff' FROM nspcl WINDOW w AS (ORDER BY id);``
by
0 like 0 dislike
If you have id without gaps go, just joinin to the sample id-1. If missing, it is a little more complicated the request is.

So if the ID in order, for example:

``SELECT t2.partnum p2, t1.partnum p1, t2.partnum - t1.partnum res FROM nspcl t1 Nspcl INNER JOIN t2 ON t1.id = (t2.id - 1)``
by
0 like 0 dislike
What version of MySql ? If 8, then using window functions the task is solved on time
\rhttps://dev.mysql.com/doc/refman/8.0/en/window-fun...

Approximately

``SELECT t1.num, t2.num, t2.num is a t1.num FROM ( SELECT num, ROW_NUMBER() OVER(ORDER BY num) AS rn FROM table ) AS t1 LEFT JOIN ( SELECT num, ROW_NUMBER() OVER(ORDER BY num) AS rn FROM table ) AS t2 ON t1.rn=t2.rn-1``

I must say that performance is not checked and written in the syntax of MSSQL, just outline the idea
by
0 like 0 dislike
"two ends, two rings, in the middle of the stud" - "points to the boob nailed"

you can count only muscul doesn't know about the ends nothing, so the query need an ordered sequence (well ID which autoincrements) and a three-story expression

with a competent ID and can be shorter
by
0 like 0 dislike
Thanks a lot to all for the help!
by

0 like 0 dislike