I have a table as follows:
I just want the previous entry for every row from the column 'marks'.
Table structure:
test_id marks
100 55
101 55
102 60
103 58
104 40
This is my query:
select *,LAG(marks,1,marks) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as prev
from student_tests
This is the output:
test_id marks
100 55
101 55
102 60
103 58
104 40
The output is as per my expectation. But, I am unable to figure out if it makes sense when I give 1 as the offset in the part LAG(marks,1,marks) along with the window.
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Because, I am in the sense asking SQL to look 'UNBOUNDED' PRECEDING and then giving 1 in the offset doesn't seem sensible to me. If my understanding is correct, query must have given me the error. But, it didn't. So, I am wondering whether there's any order of precedence on the play under the hood.
Am I missing something?
Please help me understand this.
Edit: Respecting the Stackoverflow policy, I have removed the images and tried alternatives.
The lag
and lead
functions are special, in that they ignore the frame
clause. So it makes no difference if you specify ROWS
or RANGE
or what the window is. It always just looks forwards or back the specified number of rows. You can prove this by looking at results from the lead
function. In most other DBMSs it's not even valid to specify a window frame for lead
and lag
.
The default window specification is in any case RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
, so it's not necessary to specify it even in functions where it does make sense. Moreover, RANGE
is almost never what you want, you nearly always want ROWS
.
Furthermore, lag
and lead
only make sense with a unique ORDER BY
specification, otherwise "Without ORDER BY
, rows are processed in an unspecified order". As always in SQL, if you do not specify ORDER BY
then all bets are off.
The default number of rows is 1
, so it's not necessary to specify it unless you also want to specify the default value.
So a more normal-looking lag
call would be
LAG(marks, 1, marks) OVER (ORDER BY test_id) as prev