There is something you need to keep in mind for windowing functions: they are performed on every row. Thus windowing functions like lag and lead apply to the previous and next rows. By adding an order by, you are basically saying “in this grouping, up to and including the current row, what is the max value for column item_name”.
This doesn’t make sense ...
The windowing clause you use (in this case the default of "range between unbounded preceding and current row") operates on what you order by.
There is some logic to that: to be able to know what is "preceding" or "following" you have to talk about ordered data.
In query-1 you order by item_index, which is also what you partition ...
max(t.Item_name)over(partition by t.item_index order by item_index) new_column
Let's take a group where t.item_index = 0. It is
When order by item_index is applied then all rows have the same value, hence all of them are included into the frame, and all rows values are used for MAX() selection. So the value 'E' is ...
The explanation for the different results is given in SQL Server's documentation about window functions, the ORDER BY section:
Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.
If it is specified, and a ROWS/RANGE is not ...
I wouldn't worry about hardcoding the number of months in a year
create table #t (mm int)
insert #t (mm) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
select mm, (mm + 8) % 12 from #t order by 2 asc
MIN(created_at) OVER (PARTITION BY user_id, step) created_at,
FIRST_VALUE(device) OVER (PARTITION BY user_id, step ORDER BY created_at)
|| ' > ' ||
FIRST_VALUE(device) OVER (PARTITION BY user_id, step ORDER BY created_at DESC) device_concatenated
ORDER BY user_id, step;
Given the data as is (as of 11:38 UTC, 2021-04-08), this might help (see PostgreSQL fiddle here):
CREATE TABLE tab
user_id TEXT NOT NULL,
step TEXT NOT NULL,
created_at TIMESTAMP NOT NULL,
device TEXT NOT NULL
Populate it (slightly rearranged from original data to put it in order - user_id, step & created_at - makes it easier to figure ...