3 ms·
Ask HN: What's the most valuable SQL Query you ever wrote?
The other day I could not find a copy of a SQL Query that we only use about once every 3 years for a rather obscure purpose and I realized suddenly that the query itself contained a body of both SQL and domain knowledge that I don't have anymore and I felt like I'd really lost something of great value and hard to reproduce, although it's just 5 or 10 lines of SQL.
Kinda interested to hear of other valuable queries - either in terms of the knowledge they held or the effect they had.
- thedevindevops 7y agoFUNNY you should mention that, recently a higher-up came to me with a 'bug' that consisted of the feature onboarding process for a new partner wasn't working. On investigation the configuration app we use to manage multiple client's environments had the code required to do this mysteriously disabled and re-enabling it was throwing errors. To avoid onboarding delays I was asked to retrieve the initial onboarding sql scripts for originally configuring the test environment. From an email I sent 6 months ago while an investigation as to why the config app is broken is being kicked off.
- karmakaze 7y agoThe most recent one I can think of was to renumber an ordered collection when an interval space gets full. There was a blog post but I can't find it now. Here's an executed SQL query. The source would have variables in place of most of the numeric values. I think this one finds now many rows need to be renumbered (shuffle_rank) and what the cut-off position is at that point. There's a corresponding UPDATE that does the renumber of the shuffle_rank count of rows that evenly respaces the position values between the available number space previously occupied by the rows being renumbered. SELECT shuffle_rank, position FROM (SELECT @shuffle_rank := @shuffle_rank + 1 AS shuffle_rank, position FROM `gallery_items` JOIN (SELECT @shuffle_rank := 0) initialize_rank_var WHERE `gallery_items`.`gallery_id` = 14103882 AND (position >= 120533287) ORDER BY position ASC) positionable_items WHERE ABS(120533287 - position) >= shuffle_rank + 0 LIMIT 1 They were a valuable pair of queries because it was killing the master mysql db preventing it from scaling further beyond the 10K's (of 1M+) online users. I had also written a mainframe DB2 query that spanned 2 and 1/2 letter sized pages that took several minutes to run. It made extensive use of CTEs and correlated subqueries with guard hueristics (to bound time intervals) to limit the work of exact subqueries to find something (that I can't recall now) in student loan histories. It required a couple updates/patches to DB2 to just let the should-be valid SQL to run. I probably spent about two weeks just to come up with the one query and optimize it down to that duration.