How to Find the Closest Subset Sum with SQL

I've stumbled upon this very interesting question on Stack Overflow, recently. Its title is: [How to] compare a number with sum of subset of numbers In this article, we'll compare the user's imperative approach to the extremely elegant (Oracle) SQL approach. We'll be making use of any combination of these awesome SQL features: Window functions … Continue reading How to Find the Closest Subset Sum with SQL

Recursive SQL for Data Normalisation

Recursive SQL can be awesome, although a bit hard to read in its SQL standard beauty. Let's assume you have some aggregated data with dates and a number of events per date: | DATE | COUNT | |--------------------------------|-------| | October, 01 2013 00:00:00+0000 | 2 | | October, 02 2013 00:00:00+0000 | 1 | | … Continue reading Recursive SQL for Data Normalisation

Recursive queries with Oracle’s CONNECT BY clause

Recursive or hierarchical queries are an awkward thing in SQL. Some RDBMS allow for recursiveness in Common Table Expressions (CTE's), but those queries tend to be quite unreadable. That's not the case for Oracle, which, in addition to recursive CTE's also supports a dedicated CONNECT BY clause. The general syntax for this clause looks something … Continue reading Recursive queries with Oracle’s CONNECT BY clause