First of all, thanks for clarification. Second, just to warn some people like me who could potentially be stumped by a little typo: in 6:09, in the presentation, write to the order_id 5 the date also 2022--04-03, and not 2022-03-03, otherwise we would not have a logical grouping. Again, I want to than you again Ben: only after watching this video I truly feel that I grasp the window functions completely. Kudos to your amazing clarification skills.
Another Awesome tutorial, crystal clear and concise by the SQL Master! This man is truly a legend! After numerous tutorial, I finally got my head arround the order by argument in the over ( ) clause. Thank you a lot. This is the relative sorting against or over the particular group/window.
Good question. It depends on the size of your table and several other factors, but I've found that they perform better than using a correlated subquery (which is an alternative way of getting the same data). You could test other approaches on your database and see if it is better or worse.
Can we use where clause or any kind of filters in any way for each windows function seperately ? We are unable to fully utilize window function without where clauses... or any trick to do so in sql server ?
Yes you should be able to use a window function and where clauses. The window function does the calculation and the where clause does the filtering. I don’t know if you can have separate where clauses for each window function. Perhaps surrounding it with a case statement?
When adding the partition, I accidentally did not grab the "order by" for the outer query and it worked perfectly. So rather than add additional code to make it work, makes more sense to let it run with less code? Discovered this completely by accident. Here is the code. Select order_id, order_date, order_total, SUM(order_total) OVER ( PARTITION BY order_date ORDER BY order_id ASC ) AS running_total From orders
Thanks for the comment and the code. Yes, this query would run because you don't need that final Order By statement. Without that last Order By statement, the records are not shown in any specific order. The running total calculation for each row is still correct, but because the rows can be displayed in a different order, it may seem like the running total is jumping around or not in an order. Adding the Order By to the end will make the results look clearer or look like the running total is being shown in the right order. But, without it, the calculation is still correct as you have mentioned.
Yeah you could achieve a similar result. However, window functions allow you to perform a calculation on one group of data and display the data in another group. A Group By will both display and calculate on the same group of data.
Hey man, glad to meet again. I have only a simple enquiry as a new sql learner. How order by clause differs from rank function in sql? Thanks for taking care of this.
Good question. The Order By clause will sort the data in the order you specify. The Rank function will calculate where a row will fit in an order based on a criteria, but it doesn't change how the data is ordered.
@@DatabaseStar I learned that rank () should be accompanied with over and order by clauses....So rank does dictate a change in the order of records? is that true? Here a sample of rank function in my query select id, rank() over(order by id asc) from val; Thanks
appreciate the video - but you're introducing concepts a little too quickly here - namely the frame -- the default frame - unbounded preceding to the current row - and this is a little too tricky for people to grasp without a direct explanation - the running total would make sense in light of the default frame.
First of all, thanks for clarification. Second, just to warn some people like me who could potentially be stumped by a little typo: in 6:09, in the presentation, write to the order_id 5 the date also 2022--04-03, and not 2022-03-03, otherwise we would not have a logical grouping. Again, I want to than you again Ben: only after watching this video I truly feel that I grasp the window functions completely. Kudos to your amazing clarification skills.
Great, thanks for the correction and advice!
Thanks, Ben! You nailed in the explanation and can show things in a very clear way.
Thanks!
You have done great when it comes to good video creation and Knowledge delivery. Nice! Learned what I needed. Nice!
Thanks! I’m glad you liked the video.
you have the best SQL playlist, Thank you
Thanks!
Great teacher!!! One of the best I have seen.
Thanks a lot!
Another Awesome tutorial, crystal clear and concise by the SQL Master! This man is truly a legend! After numerous tutorial, I finally got my head arround the order by argument in the over ( ) clause. Thank you a lot. This is the relative sorting against or over the particular group/window.
Thanks! I'm glad it helped you understand.
This is honestly such an amazing video. Thank you so much.
Thanks! Glad you like the video.
Clear & informative tutorial, thank you!
Glad it was helpful!
Thank you so much for this. It was very clear and helpful.
Glad it was helpful!
very well explained
Thanks!
Great Video!
Thanks!
YOU ARE THE BEST MATE!
Thanks!
Thanks, this was helpful.
You're welcome!
Well presented!
Thank you!
Great explanation. Thanks!
You're welcome!
This was very helpful! TY
Thanks!
Really helpful! Thanks!
Glad it was helpful!
Are te windows function good for performance compared to other options retriving same results?
Good question. It depends on the size of your table and several other factors, but I've found that they perform better than using a correlated subquery (which is an alternative way of getting the same data). You could test other approaches on your database and see if it is better or worse.
Can we use where clause or any kind of filters in any way for each windows function seperately ?
We are unable to fully utilize window function without where clauses... or any trick to do so in sql server ?
Yes you should be able to use a window function and where clauses.
The window function does the calculation and the where clause does the filtering.
I don’t know if you can have separate where clauses for each window function. Perhaps surrounding it with a case statement?
@@DatabaseStar can you please advise how to use where clause along with over clauses in windows function ?
great video! very helpful! 😊
Glad it was helpful!
When adding the partition, I accidentally did not grab the "order by" for the outer query and it worked perfectly. So rather than add additional code to make it work, makes more sense to let it run with less code? Discovered this completely by accident. Here is the code.
Select order_id,
order_date,
order_total,
SUM(order_total) OVER (
PARTITION BY order_date
ORDER BY order_id ASC
) AS running_total
From orders
Thanks for the comment and the code. Yes, this query would run because you don't need that final Order By statement.
Without that last Order By statement, the records are not shown in any specific order. The running total calculation for each row is still correct, but because the rows can be displayed in a different order, it may seem like the running total is jumping around or not in an order.
Adding the Order By to the end will make the results look clearer or look like the running total is being shown in the right order. But, without it, the calculation is still correct as you have mentioned.
Could you not achieve the same results using a GROUP BY clause?
Yeah you could achieve a similar result. However, window functions allow you to perform a calculation on one group of data and display the data in another group. A Group By will both display and calculate on the same group of data.
How to databases configuration?
What do you mean?
I like the video and the channel content looks cool => subscribed
Thanks! 😀
Nice 👍
Thanks!
Hey man, glad to meet again.
I have only a simple enquiry as a new sql learner.
How order by clause differs from rank function in sql?
Thanks for taking care of this.
Good question. The Order By clause will sort the data in the order you specify. The Rank function will calculate where a row will fit in an order based on a criteria, but it doesn't change how the data is ordered.
@@DatabaseStar
I learned that rank () should be accompanied with over and order by clauses....So rank does dictate a change in the order of records? is that true?
Here a sample of rank function in my query
select id, rank() over(order by id asc)
from val;
Thanks
BigQuery as well
Good idea
👍👍
Thanks!
💙
Thanks!
♥️🙏🤝
Thanks!
appreciate the video - but you're introducing concepts a little too quickly here - namely the frame -- the default frame - unbounded preceding to the current row - and this is a little too tricky for people to grasp without a direct explanation - the running total would make sense in light of the default frame.
Thanks for the feedback! Window functions are a hard concept to understand and explain, so I appreicate the feedback on how I've explained it!
Very helpful, Thanks!
Good to hear!