New answers tagged

1

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 ...


3

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 ...


3

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 Item_index Item_name 0 A 0 C 0 E 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 ...


5

The explanation for the different results is given in SQL Server's documentation about window functions, the ORDER BY section: ORDER BY 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 ...


1

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


0

SELECT DISTINCT user_id, step, 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 FROM tab ORDER BY user_id, step; ...


0

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 ...


Top 50 recent answers are included