Question 2

Question

Hello,

Would you please help me with this one?

Thank you in advance,
Michael

Answer 1

"putting it another way - why does the Preceeding column looks correct (from 1 to 10) while the the Following column ~has another ordering?"

Whenever you do a sort, if there are ties, the ties can come out in any order. You have four sorts in that query (the three OVER clauses and then the final ORDER BY). As long as there are ties, you have no control over the order of the tied items. They could all be the same in each sort, or they could be different. They could be the same the first time you run the query and then be different the next time you run the query.

If you care about the the order of tied items, you must provide enough info in the ORDER BY to be sure there are no ties. The usual way to do that is to use the Primary Keys. So, for example, if the primary key of your Inventory table is Warehouse, Product, BinNbr, then you would want to have those in your ORDER BY's. You already have Product, so just add the other keys and your query would look like

Select warehouse, Product, quantity,
  Preceding = Count(*) Over(Order By Product, Warehouse, BinNbr Rows Between Unbounded Preceding And Current Row),
  [Three current] = Count(*) Over(Order By Product, Warehouse, BinNbr Rows Between 1 Preceding And 1 Following),
  Preceding = Count(*) Over(Order By Product, Warehouse, BinNbr Rows Between Current Row And Unbounded Following)
From Inventory
Order By Product, Warehouse, BinNbr;

Tom

Answer 2

You probably observe how indeterminate is the ordering when there are repeated values. For example, with your main ‘order by Product’, it is theoretically possible to obtain various combinations of "iPhone", then "Samsung", rows. The same thing happens with ‘order by’ used for count().

The shown picture is not the only possible result. Seeing something like ‘3, 2, 6, 1, 4, 5’ instead of ‘1, 2, 3, 4, 5, 6’ in [Preceding] column is a valid result too.

If your table has an ID (key) column, you can try 'order by Product, ID' to get a more deterministic order.

Answer 3

"The shown picture is not the only possible result. Seeing something like ‘3, 2, 6, 1, 4, 5’ instead of ‘1, 2, 3, 4, 5, 6’ in [Preceding] column is a valid result too." - I thought of it... Moreover, I think that could be the only possible explanation - but I think in this case the order of the values in the Preceding column should follow the order of the values in the Following column to always make 10 in total (for example, if the Preceeding = 3 then Following = 7) and there should never be the combinations like Preceeding =6 and Following = 10. Am I wrong?

P.S. putting it another way - why does the Preceeding column looks correct (from 1 to 10) while the the Following column ~has another ordering?

Answer 4

Hi @Mikhail Firsov ，

Please try:

    ;with cte  
     as(Select warehouse, Product, quantity,  
       Preceding = Count(*) Over(Order By Product, Warehouse, BinNbr Rows Between Unbounded Preceding And Current Row),  
       [Three current] = Count(*) Over(Order By Product, Warehouse, BinNbr Rows Between 1 Preceding And 1 Following),  
       [Following] = Count(*) Over(Order By Product, Warehouse, BinNbr Rows Between Current Row And Unbounded Following)  
     From Inventory)  
      
     Select * from cte  
     Order By [Following] Desc  

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

Thank you all so much for your replies!!!

"Whenever you do a sort, if there are ties, the ties can come out in any order. " - I thought of it but was confused by the fact that the order of the values in the Preceeding and Three Current columns was always the same - it was only the Following column that displayed different ordering.
Once I've added the other two columns to all Order BY statements (and those columns are not even the Primary Key - the Inventory table does have the PK at all) the output started looking as expected:

The trick with CTE is also great!

Thank you all for your help once again!

Regards,
Michael