Thank you for such a well done video. I subscribed because I just finished my first mySQL Database Management class. I have to re-do the class and I think your videos will be a big help.
Hey Bert thanks for the amazing tip. I would really appreciate if you can answer this, is there any problem that absolutely requires correlated sub queries that cannot be solved by joins or derived tables.? I don’t like using them because they confuse me. Thanks.
Thanks Bert. I tried your and got different results. I do want to throw another query out, and get your take on it... In the past, I have achieved great performance gains with CROSS APPLY statements with a TOP in it, but when I tested it in this scenario, the results were atrocious. CPU time and reads went through the roof... The query is select DISTINCT o.UserID ,S.FirstBadgeDate from Badges o CROSS APPLY ( select top 1 [Date] as FirstQuoteDate from Badges b WHERE b.UserID = i.UserID Order by b.Date desc ) S Any idea why it went so wrong?
Good stuff as always. BTW I have never seen the syntax like (FirstBadgeDate = ) in a select statement. Can you take a moment to explain that, or give it a name so I can research it? I have seen something like that where it was referred to as a quota query but it was in the WHERE clause, like WHERE 2 = (select COUNT(*) from employee EE where E.employeeID = EE.employeeID). I think it was Tony Andrews doing a talk on optimizing DB2 queries where he called it a quota query.
Thank you for such a well done video. I subscribed because I just finished my first mySQL Database Management class. I have to re-do the class and I think your videos will be a big help.
Hey Bert thanks for the amazing tip. I would really appreciate if you can answer this, is there any problem that absolutely requires correlated sub queries that cannot be solved by joins or derived tables.? I don’t like using them because they confuse me. Thanks.
True , They are nasty 🥺
I have knowed already it but brilliant presented. Thx
Depending on the original query window function could the trick too... but .. as always... it depends...
Thanks Bert. I tried your and got different results. I do want to throw another query out, and get your take on it... In the past, I have achieved great performance gains with CROSS APPLY statements with a TOP in it, but when I tested it in this scenario, the results were atrocious. CPU time and reads went through the roof...
The query is
select DISTINCT
o.UserID
,S.FirstBadgeDate
from Badges o
CROSS APPLY
(
select top 1 [Date] as FirstQuoteDate
from Badges b
WHERE b.UserID = i.UserID
Order by b.Date desc
) S
Any idea why it went so wrong?
You’re also selecting the last date, not the first 🤔
Good stuff as always. BTW I have never seen the syntax like (FirstBadgeDate = ) in a select statement. Can you take a moment to explain that, or give it a name so I can research it? I have seen something like that where it was referred to as a quota query but it was in the WHERE clause, like WHERE 2 = (select COUNT(*) from employee EE where E.employeeID = EE.employeeID). I think it was Tony Andrews doing a talk on optimizing DB2 queries where he called it a quota query.
@@DataWithBert Thanks Bert. Looks like I was overthinking it and I didn't recognize it as a column alias.
Super helpful videos.
nice vid. useful. thx
The Final Solution