Fetching the first N rows from a result set is easy with the fetch first clause. But what if you want the top-N rows in each group? For example, the three most...
Fetching the first N rows from a result set is easy with the fetch first clause. But what if you want the top-N rows in each group? For example, the three most recent orders for each customer? There's no native syntax for this in SQL. In this post we'll explore how to do this!
Fetching the first N rows from a result set is easy with the fetch first clause. But what if you want the top-N rows in each group? For example, the three most recent orders for each customer? There's...
An overview of business problems you can solve using SQL pattern matching, including: Finding consecutive rows; Grouping rows into batches, with unknown gaps in...
An overview of business problems you can solve using SQL pattern matching, including: Finding consecutive rows; Grouping rows into batches, with unknown gaps in between; Solving bin packing problems; Searching for sequences of events
An overview of business problems you can solve using SQL pattern matching, including: Finding consecutive rows; Grouping rows into batches, with unknown gaps in between; Solving bin packing problems;...
The Olympics is over for another year. But there's still plenty of time for SQL-style data wrangling of the results! To do this, I've compiled a table of medal...
The Olympics is over for another year. But there's still plenty of time for SQL-style data wrangling of the results! To do this, I've compiled a table of medal winners from Rio for each sport This is great when looking for a specific result. But what everyone really wants to know how their country fared overall. To get this you need to convert the table above to the final medal table: To do this, you need to count the number of gold, silver and bronze rows for each country....
The Olympics is over for another year. But there's still plenty of time for SQL-style data wrangling of the results! To do this, I've compiled a table of medal winners from Rio for each sport This...
An explanation of how to find rows with duplicate values in a table using SQL. And delete them. Finishes by showing how to stop people entering new duplicates!
Imagine you have a table with a series of transactions. These are classified into credits or debits: TRANS_ID TRANS_DATETIME TRANS_TYPE AMOUNT --------...
Imagine you have a table with a series of transactions. These are classified into credits or debits: TRANS_ID TRANS_DATETIME TRANS_TYPE AMOUNT -------- ----------------- ---------- -------- 6 01-JAN-2015 00:00 CREDIT 40 7 01-JAN-2015 00:00 CREDIT 90 8 01-JAN-2015 00:00 DEBIT 80 5 02-JAN-2015 00:00 CREDIT 200 4 03-JAN-2015 00:00 DEBIT 99 3 04-JAN-2015 00:00 CREDIT 50 2 05-JAN-2015 00:00 DEBIT 200 1 06-JAN-2015 00:00 DEBIT 100 Your boss has asked you to do the following: "I...
Imagine you have a table with a series of transactions. These are classified into credits or debits: TRANS_ID TRANS_DATETIME TRANS_TYPE AMOUNT -------- ----------------- ---------- --------...
In the previous post we looked at stock market investment strategies. It looked like we'd hit on a winning method: buy $100 of shares every time the S&P 500...
In the previous post we looked at stock market investment strategies. It looked like we'd hit on a winning method: buy $100 of shares every time the S&P 500 falls 2% or more during a day. This provided the best return on our money. As hinted, there are problems with this method though. Let's find out what these are and see if we can mitigate them. We'll use SQL magic to do this, including a look at the model clause. First, let's review how feasible the 2% buy rule is. Our...
In the previous post we looked at stock market investment strategies. It looked like we'd hit on a winning method: buy $100 of shares every time the S&P 500 falls 2% or more during a day. This...
What's the best strategy for investing in the stock market? Simple - buy low and sell high. There's big a catch to this method. We don't know in advance what...
What's the best strategy for investing in the stock market? Simple - buy low and sell high. There's big a catch to this method. We don't know in advance what the highs and lows are! Only by looking back can we know the best times to buy and sell. So which approaches can we use? And, more importantly, how much money can we make by following them? In this post we'll use SQL to calculate stock market returns for different purchasing strategies. The purpose is to show how to...
What's the best strategy for investing in the stock market? Simple - buy low and sell high. There's big a catch to this method. We don't know in advance what the highs and lows are! Only by...
A colleague recently posed me the following question: I want to find a row matching a string and the N (non-matching) rows either side of it, ordered by primary...
A colleague recently posed me the following question: I want to find a row matching a string and the N (non-matching) rows either side of it, ordered by primary key. How can I do this using SQL? It's an interesting problem. Let's look at how you do in this in a single, efficient select statement. When constructing SQL queries it's always beneficial to have example data along with the expected output. In this case, given the following rows: ID STRING -- ------ 1 abc 2 def 3...
A colleague recently posed me the following question: I want to find a row matching a string and the N (non-matching) rows either side of it, ordered by primary key. How can I do this using SQL? It's...
Thanks to everyone who entered the Obfuscated SQL Contest! This ran on the PL/SQL Challenge from March - April. It asked players to produce ingenious,...
Thanks to everyone who entered the Obfuscated SQL Contest! This ran on the PL/SQL Challenge from March - April. It asked players to produce ingenious, creative, quirky and downright weird SQL to return the current time in the timezones Bangalore, London, New York, Chicago and Denver. There were 53 entries, many of which provoked serious head scratching amongst the judges to determine how these queries actually work! After much deliberating in an exceptionally close run...
Thanks to everyone who entered the Obfuscated SQL Contest! This ran on the PL/SQL Challenge from March - April. It asked players to produce ingenious, creative, quirky and downright weird SQL to...
