4

I have a table with below structure and it's data :

create table test_table
(
Item_index   int,
Item_name    varchar(50)
)

insert into test_table (Item_index,Item_name) values (0,'A')
insert into test_table (Item_index,Item_name) values (1,'B')
insert into test_table (Item_index,Item_name) values (0,'C')
insert into test_table (Item_index,Item_name) values (1,'D')
insert into test_table (Item_index,Item_name) values (0,'E')

I want to know why changing the column in order by section of the query , changes the result? In QUERY-1 , I used item_index and in the QUERY-2 I used item_name column in the order by section. I thought that both queries must generate the same result because I used item_index in both queries for partitioning! I'm completely confused now ! why the order by column should affect the final result?

QUERY-1:

select t.*,
       max(t.Item_name)over(partition by t.item_index order by item_index) new_column
from test_table t;

RESULT:

Item_index  Item_name     new_column
----------- --------------------------
0           A                E
0           C                E
0           E                E
1           D                D
1           B                D

QUERY-2:

select t.*,
       max(t.Item_name)over(partition by t.item_index order by item_name) new_column
from test_table t;

RESULT:

Item_index  Item_name  new_column
----------- -----------------------
0           A             A
0           C             C
0           E             E
1           B             B
1           D             D

Can anybody explain how exactly these two queries are being executed and why each of them generates different result?

Thanks in advance

1
  • what I mean is that I'm using the same column for partitioning which is ITEM_INDEX , so the max(ITEM_NAME) must be fix , even if I change the order by column.In the first partition , "E" is max and in the second partition "D" is max , no matter what is being used in the order by section !!
    – Pantea
    Apr 19 at 6:55
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 returned for all rows.


max(t.Item_name)over(partition by t.item_index order by item_name)

Let's take the same group.

Item_index Item_name
0 A
0 C
0 E

Now the sorting key differs, and when the window RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is applied then different rows are included into the frame to be investigated.

For 1st row only this row is included into the frame, and 'A' is the only value in the frame, so it is returned.

For 2nd row first 2 rows are included into the frame, the values 'A' and 'C' are compared, and 'C' is returned as maximal value in the frame.

For 3rd row all 3 rows are included into the frame, the values 'A', 'C' and 'E' are compared, and 'E' is returned as maximal value in the frame.

4
  • Thanks for your answer , but again I did not understand , look , based on the documentation , "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" applies to both queries so both of them must produce the same result set !! again I'm completely confused !!
    – Pantea
    Apr 19 at 11:44
  • 1
    @Pantea "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" in more detailed description means starting from the first row (in specified ordering) until all rows with the sorting expression value euqal to one in current row. In the first option we sort by item_index. First row have item_index=0 (least value is 0). Current row have item_index=0, so the frame includes all rows which have item_index from 0 (least value) to 0 (current value) - i.e. all 3 rows. Current sorting expression does not distinguish one zero from another zero, all of them matches the range.
    – Akina
    Apr 19 at 12:00
  • OK . Nooow I undestood !! Give me the right please ! it WAS a bit difficult isn't it? thanks for your answer and extra explanation.
    – Pantea
    Apr 19 at 12:08
  • 1
    .... ordering by the partition value should give an undefined result, though, since you're ordering by a constant value - I'm not convinced that the result for case 1 is guaranteed by spec (although as an implementation detail it may be elided), especially as table/intermediate result sizes gets larger. @Pantea - You should probably just leave that ORDER BY off, since ordering by a constant value at best is elided, and otherwise may just cause your system to do extra work before returning undefined results. Apr 20 at 6:14
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 specified, then default RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame by the functions that can accept optional ROWS/RANGE specification (for example min or max).

Note that MIN() and MAX() window aggregates accept an optional ROWS or RANGE specification.

When there is no such specification, they calculate the MIN and MAX over the whole partition. When there is, they calculate the MIN or MAX over the specified range. Since your two queries specify different orders/ranges, they yield different results.

If you wanted the MAX over the whole partition, then remove the ORDER BY range:

If it is not specified, the default order is ASC and window function will use all rows in partition.

2
  • So , I used an order by but did not specify the "ROWS/RANGE" neither in the first query nor in the second one , SO both of them must produce the same result , I'm confuse again cause based on the documentation "RANGE UNBOUNDED PRECEDING AND CURRENT ROW" must be used for both queries so ---> both should produce the same result .
    – Pantea
    Apr 19 at 11:47
  • 1
    @Pantea correct, RANGE UNBOUNDED PRECEDING AND CURRENT ROW will be used for both queries. But the ranges are different because the order is different. Every partition has a set number of rows but the ORDER BY provides an order for each partition. So all the rows of a partition are ordered based on the ORDER BY and then (each row) MAX() OVER () calculation will find the maximum over the specified range which starts with the first row of the partition until the current row. Apr 19 at 12:54
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 by. So let's look at the partition with item_index = 0. Every row in the partition wil have an item_index 0. That means that that for each row the window will effectively be "All rows with an item_index <= 0". So, basically all rows in the partition. And the max(item_name) of that is 'E'.

In query-2 you order by item_name. For that same partition that means that the window for the 'A' row will be "All rows with an item_name <= 'A'" That is just the 'A' row, so the max(item_name) is 'A'. The window for the 'C' row will be "All rows with an item_name <= 'C'" That's the 'A' and 'C' row, so the max(item_name) is 'C' . The window for the 'E' row will be "All rows with an item_name <= 'E'" That's the 'A', 'C' and 'E' row, so the max(item_name) is 'E' .

Hope this helps to visualise what happens.

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 when using max on the same column as you are ordering by (assuming asc), as it is always going to the same as the current column, but it makes sense for min and avg. And having it do anything else for max would be way too complicated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.