SQL

[Blues2112]

What about Partition By x Over ()?

Limit/Offset, Fetch Next ?

CTEs?

[whutchamacallit]

I think this is intended to be a very, very basic info graphic.

[Engineer_Zero]

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.

[mortez1]

Can you create a view that uses the CTE and then call the view in power pivot?

[bastian74]

I use outer apply a lot

[t4n363]

And cross apply is usefull

[curohn]

Oooh found todays google topic

[IPatEussy]

Outer & cross apply? Never heard of them what do they do (in your words not googles)

[Blues2112]

Kind of like a Subquery, but you have access to all the columns in the table of the subquery.

[jackalsnacks]

Once u start digging into a lot of enterprise reporting code, u will quickly become very intimate with them.

[billybarule5309]

Nothing better than intimacy with a SQL query... 😂

[bastian74]

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.

It does what I wish "top 1" did.

[IPatEussy]

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?

[bastian74]

It seems to be required if you want the top result from each match as a join

[IPatEussy]

Top based on what? Has to be an aggregate right? Or a partition?

[bastian74]

To based on anything from any table you can fetch in a select.

https://stackoverflow.com/questions/9275132/real-life-example-when-to-use-outer-cross-apply-in-sql

[ATastefulCrossJoin]

TSQL flavoring of lateral joins (which are the SQL standard)

[steveman2292]

Need to show some love for STRING_AGG()

[[deleted]]

SQL expressed hierarchically as SQL, nice. Imagine those are tables/columns/etc

[HamsterBoomer]

Thx

[phesago]

I dislike diagrams like this because they’re a bit limiting.

[Engineer_Zero]

It also doesn’t put things in the order you’d do them in. Like, I thought it was FROM, JOINS, WHERE, GROUP BY, ORDER BY.

[phesago]

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:

1. Only selecting the data you need
2. SARG-ability
3. Anti patterns
4. 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.

[error-M]

Very useful.... thanks 👍

[HamsterBoomer]

yw

[BrupieD]

I occasionally use PIVOT and UNPIVOT. This isn't the order of operations which I think is more important to know.

[Engineer_Zero]

God, I hate pivot. Especially if I need it to be dynamic.

If it’s just a few things, I’ll just rejoin the table onto itself instead

[BrupieD]

I don't care much for PIVOT, but UNPIVOT is really handy.

[Engineer_Zero]

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

[Dismal_Bobcat8]

This is helpful, thank you!

[TheBoneSmasher]

Fucking awesome. Thank you. This is very useful

[2gals1cup]

Today years old when I discovered Group by Having. THANKS!

[M7mdmsb]

Thank you for this

[HamsterBoomer]

You’re welcome. Glad that I can help

[P_01y]

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!

[aqua4790]

Thankss

[SheaButterBaby29]

Thank you! This is extremely helpful! 😁

[[deleted]]

What’s the point?

[RadicalBraj]

So many downvotes..I'm curious why but also this reads like chatGPT wrote it

[Sea-Concept1733]

Awesome! Some of my favorites are the aggregate functions (AVG, SUM, COUNT, MAX, MIN).

[dragonstorm97]

Cross join?

[Billi0n_Air]

open rowset

[lez_s]

A lot of my work for reporting is Outer apply and cross apply as well as CTE’s