Ian Turton

Recent Blog Comments By Ian Turton

• Using Multiple Common Table Expressions In One SQL Query In MySQL

  Posted on Jun 3, 2024 at 11:49 AM

  @Ben Nadel, sorry Ben - my article comment was tongue in cheek. I was imagining a universe where MySQL's ability to not have to include all non-aggregate columns in the GROUP BY didn't exist currently, and was then brought into an upcoming version. Thereby allowing calculated columns to ... read more »

• Using Multiple Common Table Expressions In One SQL Query In MySQL

  Posted on Jun 3, 2024 at 11:25 AM

  @Ben Nadel, Mine is a query I'd hope would work, as for me it's the most direct/understable conversion from English to SQL, given the output requirements. And I'd point out the (easily inferred) intention matches the result! In other universe, version 8.5 of MySQL removes the requirement... read more »

• Using Multiple Common Table Expressions In One SQL Query In MySQL

  Posted on Jun 3, 2024 at 10:50 AM

  @Ben Nadel, The reason it 'works' in MySQL (this is a MySQL specific article) is that MySQL doesn't insist you include all the non-aggregate columns in the GROUP BY. My query wouldn't work in e.g. Postgres, as Postgres would insist I also included e.email in my GROUP BY, which would stop... read more »

• Using Multiple Common Table Expressions In One SQL Query In MySQL

  Posted on Jun 2, 2024 at 5:45 PM

  Just checking that this is a 'forced example on purpose', given the same result can be achieved with a simple GROUP BY on the calculated domain column; WITH emails ( 'email' ) AS ( VALUES ROW( 'laura@acme.example' ), ROW( 'tim@acme.example' ), ROW( 'anna@masters.ex... read more »

• Using LATERAL Joins To Get "Top N" Records From Each Group In MySQL 8.0.14

  Posted on Dec 2, 2022 at 6:51 PM

  As regards any 'speed' issues... The alternative to doing it in one query (and the way most devs would approach it) would one db call to get the members and a 10x loop to get the 5 top messages. That's 11 times the number of (admittedly simpler) query parsing/optimisations and 11 lots of net... read more »

• A Database Column For "Date Updated" Has No Semantic Meaning, Nor Should It

  Posted on Nov 15, 2022 at 11:48 AM

  @Ben, "I like to avoid nullable columns wherever possible". Me too, absolutely. Hate the bloody things. For var/chars I never use them, because I've never come across (others will have I suppose) the need to know whether an empty string is 'really empty' or 'don't know yet'. But ... read more »

• A Database Column For "Date Updated" Has No Semantic Meaning, Nor Should It

  Posted on Nov 15, 2022 at 11:03 AM

  @Ben, Ironically (is it, I don't know!) the component I mentioned doesn't bother with deletes because I don't do them. Can't think of the last time I did an 'app based' hard delete on a table (certainly not one anyone would care about me auditing)... read more »

• A Database Column For "Date Updated" Has No Semantic Meaning, Nor Should It

  Posted on Nov 15, 2022 at 10:59 AM

  @SCOTT, I also do both, and like you only audit a few 'major' tables in any given system. I built a component that can take an insert/update SQL statement, run it, check for triggers and create a table-specific audit record with just the changed columns. I do find 'lastUpdated' occassional... read more »

• A Database Column For "Date Updated" Has No Semantic Meaning, Nor Should It

  Posted on Oct 28, 2022 at 4:48 PM

  @Andrew, Same! I note in passing Ben's code will set updatedAt even if nothing is updated. UPDATE CURRENT_TIMESTAMP doesn't do that. (I'd also make the obvious-in-case-it's-not-to-some point that CURRENT_TIMESTAMP saves you having to remember to update that date anywhere else you updat... read more »

• It's OK To Be Super Explicit In Your Data Access Method Names

  Posted on Jul 3, 2022 at 8:57 PM

  @Ben, Sweet! 🤓... read more »

• It's OK To Be Super Explicit In Your Data Access Method Names

  Posted on Jun 20, 2022 at 12:28 PM

  @Ben, Yes! ...and the reason you're not getting it from the docs (IMO) is they've gone straight to a slightly obfuscatory, non-obvious example. Whereas way back when I found VALUES() I came to it from a 'there must be a way for me to stop repeating all these params cos it's dangerous/ted... read more »

• It's OK To Be Super Explicit In Your Data Access Method Names

  Posted on Jun 20, 2022 at 12:07 PM

  @Ben, I reckon the disconnect here is you think I'm trying to 'improve' your final query, the one that gets the id in the event that the upserts ends up being an update. I'm not - it's about the first upsert I'm trying to fix the issue that, using your original upsert example, you have to ... read more »

• It's OK To Be Super Explicit In Your Data Access Method Names

  Posted on Jun 18, 2022 at 11:06 AM

  @Ben, you can use the VALUES(col_name) function to refer to column values from the INSERT portion That's it right there. I don't know why they've gone immediately to an example of a non-obvious use for this (e.g. the single insert version of your statement I posted), but I suspect t... read more »

• It's OK To Be Super Explicit In Your Data Access Method Names

  Posted on Jun 18, 2022 at 9:36 AM

  Assuming this is MySQL, I prefer to use the VALUES function on the update part. Apart from being more readable, it removes the possibility that I make a change to the insert part and forget to change the update (or vice versa) so e.g. ON DUPLICATE KEY UPDATE valueOne = VALUES(valueO... read more »