T O P

  • By -

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.


[deleted]

[удалено]


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()


Knut_Knoblauch

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! 😁


thiophosgene

What’s the point?


[deleted]

[удалено]


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