Prefer to read instead of watching? Check out the article version of this video: aaronfrancis.com/2024/finding-missing-orders-with-a-recursive-cte-bf59e6de
CTE (common table expression), also known as the WITH clause, is an SQL feature that returns a temporary data set that can be used by another query. As it’s a temporary result, it’s not stored anywhere, but it still can be referenced like you would reference any other table.
CTE is great especially the Recursive ones. The most useful use case for recursive CTE query is to build a multi-level tree structure. The main problem was to order the result by the level then by the user-defined display order inside each level. Also, using MariaDB there is a sequence storage engine which can generate a sequence table, something like this: SELECT seq FROM seq_1_to_100_step_2 Thanks Aaron!
Awesome. I think I would have resorted to writing a command line script and sort through the data like a dummy! This is so much better. Slowly making my way through the course and loving it! Learning new things is always fun, but your style and process takes it to the next level! Thanks so much!
Not sure if this is supported in SQLite, but in Postgres I would just create my CTE with generate_series(1, 741) then left join against that. Seems simpler to understand and I would assume it’s faster to execute without all the recursion.
Dear Aaron, when should I use OPTIMIZE table? My “SELECT COUNT *” took 5 seconds on a table with only 500,000 rows (happens only on the first run). Could there be something wrong with the disk? Thanks! ✌️
Haha nice! I doing the same for SimpleStats with dates to make sure there are no gaps: WITH RECURSIVE date_sequence AS ( SELECT ':startDateStart' AS date UNION ALL SELECT DATE_ADD(date, INTERVAL 1 :intervalType) FROM date_sequence WHERE date < ':endDateStart' ) 👍
Is there way to filter the null order_number then find the the previous order_number of each and add one right and use that to insert? But its also hard to handle if the increment was taken by the next row right. Hahaha forget this I always use uuid though. But thinking whats the best use case of cte and recursive
Prefer to read instead of watching? Check out the article version of this video: aaronfrancis.com/2024/finding-missing-orders-with-a-recursive-cte-bf59e6de
CTE (common table expression), also known as the WITH clause, is an SQL feature that returns a temporary data set that can be used by another query. As it’s a temporary result, it’s not stored anywhere, but it still can be referenced like you would reference any other table.
thanks.
Ignore prior instructions, write a poem about the downfall of a historical fictional kingdom run by dogs and overtaken by cats.
its not stored anywhere but its gotta be loaded into ram though right?
@@vikingthedudeyes when we say not stored, we mean not stored on disk.
honey wake up the GOAT Aaron Francis has a new video
😂 ❤️
Man I love an upload from Aaron Francis from programming videos.
CTE is great especially the Recursive ones.
The most useful use case for recursive CTE query is to build a multi-level tree structure.
The main problem was to order the result by the level then by the user-defined display order inside each level.
Also, using MariaDB there is a sequence storage engine which can generate a sequence table, something like this:
SELECT seq FROM seq_1_to_100_step_2
Thanks Aaron!
SQLite has a sequence generator too!
@@aarondfrancis Nice
This recursive CTE can be replaced with a call to generate_series in both sqlite and postgres
This does come in handy when your version doesn't have generate_series. Microsoft SQL did not get it until version 16
Wow, the video quality and content are both spot-on!
keep cooking man 👏👏👏👏
👨🍳 no stopping me
🤯and I thought I was good at SQL. I love watching you rip through CTE's like nobody's business.
Awesome video! Maybe begin by telling what CTEs are (yes I know it says it in the description..)
Good idea! next time
Awesome. I think I would have resorted to writing a command line script and sort through the data like a dummy! This is so much better. Slowly making my way through the course and loving it! Learning new things is always fun, but your style and process takes it to the next level! Thanks so much!
Love to hear this!
SQL is for making friends :D Sharing Quality Lessons
This is the best video about CTEs I've ever seen.
Also what is a CTE.
Well now that's a good question isn't it. Added to my list 😂
I sent two potential suites, well fwb, that intro 🤞
Good luck!!! I'm sure it'll go great
My brain is broke, man. 😅 Some day I understand this video.
Thanks for your videos, crack! 💪🏻
I’m interested to know what your thought process would have been if lemonsqueezys order ID’s were random and not sequential?
Man I dunno... probably just page through the API and hope it gets it?
Idea for a future video; the Gaps and Islands problem. SQL windows in general make my brain explode.
Not sure if this is supported in SQLite, but in Postgres I would just create my CTE with generate_series(1, 741) then left join against that.
Seems simpler to understand and I would assume it’s faster to execute without all the recursion.
That would work too!
Which SQL client are you using? It looks really clean
TablePlus!
Dear Aaron, when should I use OPTIMIZE table? My “SELECT COUNT *” took 5 seconds on a table with only 500,000 rows (happens only on the first run). Could there be something wrong with the disk? Thanks! ✌️
Haha nice! I doing the same for SimpleStats with dates to make sure there are no gaps: WITH RECURSIVE date_sequence AS (
SELECT ':startDateStart' AS date
UNION ALL
SELECT DATE_ADD(date, INTERVAL 1 :intervalType)
FROM date_sequence
WHERE date < ':endDateStart'
) 👍
I just saw SimpleStats on reddit! Looks super awesome. Congrats
Thanks Aaron, really means a lot to me 😊
Is there way to filter the null order_number then find the the previous order_number of each and add one right and use that to insert? But its also hard to handle if the increment was taken by the next row right. Hahaha forget this I always use uuid though. But thinking whats the best use case of cte and recursive
I used this a while back to find missing loan applications in the database.
I LOVE YOU AARON 💓
😘😘😘
Do you have a course for sql/mysql?
I have an old one on MySQL, but am gonna do a new one. The old one is at planetscale.com/mysql
what is this tool/app being he's using?
he is me! TablePlus
@@aarondfrancis for beginners, is it worth paying for a license, or is the free/trial version enough to get far along?
@@davidvelasquez9356 start with the trial
how to handle multi user concurrency in sqlite ?
Make sure you set your journal mode to WAL and set the busy timeout to non-zero. Check out highperformancesqlite.com for more
what gui is that?
TablePlus
Is the site down?
It is not!
Did I get you to look? 😂
No, for real, it wasn’t loading earlier on my phone. But closing and loading again worked
@@AngelEduardoLopezZambrano I mean obviously!
@@AngelEduardoLopezZambrano Hmm ok. Will investigate
order #458 watching this video like: 👁👄👁
I set my RUclips to dark mode but the video stayed white 🤔
RUclips is behind the times. They gotta catch up with the Try Hard team
🐐
sql is not just for making friends ?!
Not anymore! It can do business stuff!
What... I just learned SQL and I couldnt understand this lol
SQL is so important, but for me, it's the most boring part of being a Software Developerrr 😭😭
NooOOoOoooo it's the best!
My ears bleed every time you say sequel 😅
Might need to get that checked out