Question 2

Mikhail Firsov 1,876 Reputation points
2021-03-11T11:48:00.437+00:00

Hello,

Would you please help me with this one?

76791-328.png

Thank you in advance,
Michael

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2021-03-11T15:51:29.13+00:00

    "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

    0 comments No comments

5 additional answers

Sort by: Most helpful
  1. Viorel 114.5K Reputation points
    2021-03-11T12:44:57.633+00:00

    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.

    0 comments No comments

  2. Mikhail Firsov 1,876 Reputation points
    2021-03-11T13:25:56.567+00:00

    "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?

    0 comments No comments

  3. EchoLiu-MSFT 14,581 Reputation points
    2021-03-12T01:44:01.613+00:00

    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.

    0 comments No comments

  4. Mikhail Firsov 1,876 Reputation points
    2021-03-12T09:14:43.083+00:00

    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:
    77138-q2.png

    The trick with CTE is also great!

    77177-q1.png

    Thank you all for your help once again!

    Regards,
    Michael