This is a continuation of my articles on how to select the desired rows from ranked data. A user recently posed a question in the comments that I thought was particularly intriguing:
What is the best way to query 1) Sum of min price of all types? 2) Sum of 2nd highest price of all types?
Sounds like fun! Let me start by saying the sum is the easy part. You can always do that like so:
select sum(price) from ( -- find desired rows here ) as x;
Finding the desired rows is the hard part. In my previous articles I focused on extrema:
- The single biggest/smallest/extremest row in each group. (Pretty easy.)
- The N most extreme rows in each group. (Doable, but harder.)
In this article, we’re going to see how to get not the most extreme row, not the N most …
[Read more]