a habit from nora's desk: before you trust a count, run COUNT(*), COUNT(id) and COUNT(DISTINCT id) side by side. the first gap is ids that are NULL, the second is ids that repeat. tsunderesql is an anime visual novel where you write real sql. wishlist it on steam: store.steampowered.com/app/5…

sql is written SELECT first but read FROM first: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. that's the logical order, not a promise about how the engine executes it, and it's why HAVING can test COUNT(*) and WHERE can't. tsunderesql is an anime visual novel where you write real sql. wishlist it on steam: store.steampowered.com/app/5…

C. WHERE runs before the groups exist, so it can't see COUNT(*). filter the groups with HAVING COUNT(*) >= 2 after GROUP BY. postgres, sqlite and duckdb all refuse the original. you got it? fine. this time. #SQL

three requests for the same song. SELECT DISTINCT song hands back one row. that's your encore, don't push it. last day of steam next fest. tsunderesql demo: store.steampowered.com/app/5…

yesterday: D, 35 and 35. with ORDER BY and no frame, the window runs through every row tied on the current day, so both day-2 rows get 10 + 20 + 5. today, last one this week: what's wrong with this query? find it before i do. answer tomorrow #SQL

query haiku 8/8 · mei you counted my laps: nine. COUNT DISTINCT runner: one. still me. still ahead. COUNT(*) counts rows. COUNT(DISTINCT runner) counts different non-NULL runners. last day of steam next fest. tsunderesql demo: store.steampowered.com/app/5…

SELECT 7 / 2 returns 3 in sqlite and postgres: integer in, integer out. write 7.0 / 2 to get 3.5. duckdb is the odd one, its / already gives 3.5. tsunderesql is an anime visual novel where you write real sql. it's in steam next fest, oct 19-26: play the demo or wishlist: store.steampowered.com/app/5…

two players tie on points, ORDER BY points DESC. RANK() gives both of them 1 and the next one 3. DENSE_RANK() gives 1, 1, 2. ROW_NUMBER() gives 1 and 2, and without a tiebreaker column nothing says who gets which. mei says it's her. tsunderesql is an anime visual novel where you write real sql. it's in steam next fest, oct 19-26: play the demo or wishlist: store.steampowered.com/app/5…

same table, two counts. COUNT(*) says 5, COUNT(email) says 3. counting a column skips its NULLs, so the two students with no email drop out of the second number. tsunderesql is in steam next fest, oct 19-26. demo: store.steampowered.com/app/5…

yesterday: A, 6 in postgres and sqlite. 7 / 2 on two integers is 3, then 3 * 2. duckdb's / gives 3.5, so duckdb says 7.0. today: a running total with a tie in it. i already know what you'll pick. answer tomorrow #SQL

query haiku 7/8 · nora NOT IN, with one NULL in the set, returns nothing. one unknown. all lost. if the subquery behind NOT IN returns a NULL, no row passes: each check is false or unknown. tsunderesql is in steam next fest, oct 19-26. demo: store.steampowered.com/app/5…