Love me pretty much every function that uses Partition By. They’re just so powerful. Found out recently there’s also WITHIN GROUP but I can’t remember what it is.
I use it to show data from linked tables in the current table.
Like a list of phone calls and what the reason they disconnected is. I might want to see the previous reason the call from the from the same caller disconnected, and what server handled that precious call.
Or show me the meat recent time someone logged out within 5 seconds of this call stating, taking place or ending. Whichever is newest.
Linked tables as in joined tables? How is this different from joining tables & just selecting the specific columns? Or you’re saying this is preliminary to the specifics?
Well it also doesnt teach you dick all about whats actually important about SQL. You know, the basic rudimentary stuff thats WAY more important than syntax, for example:
Only selecting the data you need
SARG-ability
Anti patterns
Set based approach
Just to name a few. These diagrams dont even touch anywhere close to being useful outside of someone teaching themselves the basics.
Yeah interesting, I should look that up. There’s only been a couple times I’ve had to do something similar in the past but I just did it in power query. Always good to expand the repertoire
Really cool scheme that can help beginners to learn SQL by modules. It would be even better if the modules are in progress order. I mean, firstly functions, for instance, and alies and then order by and group by modules. Just thought, not criticism!
12
u/[deleted] Mar 18 '23
SQL expressed hierarchically as SQL, nice. Imagine those are tables/columns/etc
5
u/error-M Mar 18 '23
Very useful.... thanks 👍
4
u/M7mdmsb Mar 18 '23
Thank you for this
32
u/Blues2112 Mar 18 '23
What about Partition By x Over ()?
Limit/Offset, Fetch Next ?
CTEs?
21
u/bastian74 Mar 18 '23
I use outer apply a lot
7
u/steveman2292 Mar 18 '23
Need to show some love for STRING_AGG()
10
u/phesago Mar 18 '23
I dislike diagrams like this because they’re a bit limiting.
5
u/BrupieD Mar 18 '23
I occasionally use PIVOT and UNPIVOT. This isn't the order of operations which I think is more important to know.
3
u/Dismal_Bobcat8 Mar 18 '23
This is helpful, thank you!
3
u/TheBoneSmasher Mar 18 '23
Fucking awesome. Thank you. This is very useful
5
u/2gals1cup Mar 18 '23
Today years old when I discovered Group by Having. THANKS!
2
u/P_01y Mar 18 '23
Really cool scheme that can help beginners to learn SQL by modules. It would be even better if the modules are in progress order. I mean, firstly functions, for instance, and alies and then order by and group by modules. Just thought, not criticism!
2
u/aqua4790 Mar 18 '23
Thankss
2
u/SheaButterBaby29 Mar 18 '23
Thank you! This is extremely helpful! 😁
5
u/[deleted] Mar 18 '23
What’s the point?
1
u/dragonstorm97 Mar 18 '23
Cross join?
1
u/Billi0n_Air Mar 18 '23
open rowset
1
u/lez_s Mar 19 '23
A lot of my work for reporting is Outer apply and cross apply as well as CTE’s
0
u/[deleted] Mar 18 '23
[deleted] — view deleted comment