In SSMS how do you 1) Select boxed pieces of SQL code as you do at 1:09:50 as when I'd try to do that normally, I'd select every line fully, not just a boxed part of the code as you would select let's say a rectangular subset of a set of folders/programs in Windows OS 2) Add commas to multiple lines as you do in 1:10:00 Many thanks for any responses!
1. hold alt 2. shift+alt+down arrow OR shift+alt+mouse click to add cursors, then you just type in what you need and it will appear on all cursors. to get back to one cursor press Esc
Got a bit confused at 26:20 because the 'ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING' doesn't give a true reverse running total. Look at the totaldue value for the top 3 rows. xxxxx994.838, xxxxx658.438, xxxxx793.798. It's jumbled the order. Sorry if this is covered later in the video. :)
It looks like the functionality sorts on column order from left to right. If you notice the values are flipped and the order values for the second column PurchaseOrderDetailID is also flipped. I have a feeling if you added this column as a secondary ORDER BY this error would be visually corrected. It seems like the partition by was calculated under this assumption but, it wasn't displayed out with this assumption.
I learnt a lot thanks, Q: how import is it to size tables so they fit cluster on the disc on a 64 bit system, or has SSD rendered this notion obsolete?
it is weird that I tried rows between 'current row and unbounded following' and got the same result as 'current row and unbounded preceding'. Could that be because of my order by selection in the window function?
@26:02 I'm trying out 'ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING' on my own dataset and I'm getting the exact same result as 'ROWS UNBOUNDED PRECEDING', essentially a running total instead of a countdown. Any idea what's causing this?
for the first business problem , we should use this query : ;with salesdetails as ( select SalesOrderID , CustomerID , TotalDue, row_number() over (partition by CustomerID order by SalesOrderID) as RN from sales.SalesOrderHeader ) select SO.CustomerID , sum( case when RN
haha.. It literally cracked me up was when he said - "I used to do stocks but then I got married". Why would he say something like that? :P However, overall it was a great session on analytical functions.
Hello mitchell, great video. One question: Why at 1:26:20 the SUM OVER PARTITION BY aggregates on the all rows (like and un-expected behavior?) instead of the default behavior which is until CURRENT_ROW. Also, is the WHERE clause affects the Window/Frame range? Thank you
