Fun Friday: What is the toughest SQL statement you ever had to write?

[KailaT]

This weeks Fun Friday topic is a suggestion from @Tristan. If you have a topic suggestion and are interested in hearing others experiences, send me a direct message by clicking on the mail icon. 

Todays Fun Friday Topic: What is the gnarliest SQL problem you had to face down?

 

[dylanwan]

Incorta joins by default find one single matching record from the parent to force the join to be a parent-child join.  Incorta also allows you to join to the first record by sorting it first according to a second criteria.  

Both of the above are actually not straightforward to do in SQLs.

[Tristan]

Back in my consulting days I used to run into some pretty hairy SQL statements with unions across multiple driving tables each with its own set of joins and running into thousands of lines.  I would spend time building up the statements condition by condition only to find out that once everything was put together, the performance was unbearably slow.  Even returning result sets in the thousands of rows could take forever.  So then the effort would switch over to a tuning exercise.  That could be good fun and I always felt like I learned something even if I ended up bringing in a DBA to help me out.  I have been kicked upstairs and so I am no longer hands on but while I can't say I miss having to write complex SQL, I can say that my memories of doing it are burned in deep!

[JoeM]

Back in the first year or two of my career, I spent my time QA'ing the logic Informatica workflows by writing parallel SQL statements with the same source to target mappings provided to the data engineers. While much of it wasn't complex, it was certainly monotonous. Most of my memories revolve around troubleshooting long running SQL statements. Issue, fix, run for 10+ min, issue, fix, run for 10+ min... 

[marcpaige]

XPath. Need I say more? No, seriously, XPath queries are a nightmare. When MS added JSON support, it was so much easier to use, but that meant schema changes from XML fields to JSON fields (a bigger deal...) Eventually, we made UDFs that encapsulated the XPath queries (which performed better for some reason?!?)