@gabbbybar

Custom Aggregate Function (to use with GROUP BY)

Jul 10, 2025 · 2 min read · Question: I'm wondering if it's possible to create a custom SQL group by function. For example, I often need to find the most common value within a group. While I can achieve this using CTEs or subqueries, having a custom group by function like the e...

Update table according with JSON/JSONB

Jul 10, 2025 · 2 min read · Question: I have a table books : Name of columnType idint (PK) nametext nb_pagesint And a table book_modifications : Name of columnType idint (PK) old_datajsonb new_datajsonb book_idint (FK) Now let's imagine I have a row in my...

Query run fast from MySQL CLI but very slow on R2dbcTemplate Code

Jul 10, 2025 · 3 min read · Question: Currently, I having a query SELECT al.category, IFNULL(jt.name, 'Ungrouped Devices') AS device_group_name, IFNULL(jt.id, '00000000-0000-0000-0000-000000000000') AS device_group_id, jt.color_code AS color_code, COUNT(*) AS total FR...

How can I SELECT rows where a JSON key is bigger/smaller than a certain date?

Jul 10, 2025 · 1 min read · Question: My table contains a value json-column: { "id":"string", "user":int, "history":{ "unixtimestamp":{ "progress":"string", "editorId":int }, "unixtimestamp":{ "progress":"s...

How to simulate a deadlock on a row in MySQL?

Jul 10, 2025 · 2 min read · Question: To simulate a lock in mysql I can grab the row with the following: BEGIN; SELECT * FROM table WHERE id=1 FOR UPDATE; Now, if I try and update that row (from another connection) it will raise the following error after innodb_lock_wait_timeo...