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
@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?
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?
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.
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
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?
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.
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
![Beginner to T-SQL [Full Course]](http://i.ytimg.com/vi/cACat4KNncg/mqdefault.jpg)
![SSIS For Beginners [Tutorial]](http://i.ytimg.com/vi/f7UKX5MjZA8/mqdefault.jpg)
This guy is a talented teacher.
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
The sound breaks occasionally.
Gets down to business at 03:19
Thx
Thanks Mitchell! Well explained
A CTE can also be preceded with GO instead of a semicolon. GO might be a little bit more readable for some.
Very helpful intro, thanks a lot Mitchell!
hey mitchell ,do i have to see any pre videos before jumping to this
Hi Darshan, you don't need to watch any pre videos before this one.
thank you so much sir. very helpful . God bless you
@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?
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?
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.
Thanks , Easy to Understand
Hello, How to get the DEMO's SCRIPTS of the webinar?
hello do you give private lesson
We do have a virtual mentoring service here: pragmaticworks.com/virtual-mentoring/
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
'MicrosoftStockHistory' table doesn't exist in AdventureWorks2016 database. Where should I get it?
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?
Wow is this the same Mitchelle Pearson wgo teaches Azure on you tube? If yes tgen big fan
It is! Thanks Junaid, thanks for the kind comments. -Mitchell Pearson
Hello Mitchell, don't have access to AdventureWorks2016 database - to be able to see MicrosoftStockHistory table. Any idea pls?
Thanks Good tutorial
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.
He didn't wanna engage in risky business which revolves around probability especially after a responsible step like marriage.
Derived tables are great if u need to use sql as a view. CTEs cannot be made to views.
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
Sorry sound brakes off.
Thanks for the feedback.
Please stop saying "kind of".