
Ask HN: What's the most valuable SQL Query you ever wrote? - scandox
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&#x27;t have anymore and I felt like I&#x27;d really lost something of great value and hard to reproduce, although it&#x27;s just 5 or 10 lines of SQL.<p>Kinda interested to hear of other valuable queries - either in terms of the knowledge they held or the effect they had.
======
karmakaze
The 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.

------
thedevindevops
FUNNY 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.

