wildest dating show moments videos Using speed dating techniques

There are other ways to phrase the statement, but they all boil down to the same thing, and they’re all pretty unwieldy and inefficient.There’s a better way: select the variety from each type where the variety is no more than the second-cheapest of that type.This is elegant, and lets you vary N without rewriting your query (a very good thing!

using speed dating techniques-72

This question comes up over and over on IRC channels and mailing lists. I want to select the cheapest fruit from each type.

Here’s the desired result: +--------+----------+-------+ | type | variety | price | +--------+----------+-------+ | apple | fuji | 0.24 | | orange | valencia | 3.59 | | pear | bartlett | 2.14 | | cherry | bing | 2.55 | +--------+----------+-------+select type, min(price) as minprice from fruits group by type; +--------+----------+ | type | minprice | +--------+----------+ | apple | 0.24 | | cherry | 2.55 | | orange | 3.59 | | pear | 2.14 | +--------+----------+ Step two is to select the rest of the row by joining these results back to the same table.

Since the first query is grouped, it needs to be put into a subquery so it can be joined against the non-grouped table: select f.type, f.variety, f.price from ( select type, min(price) as minprice from fruits group by type ) as x inner join fruits as f on = and f.price = x.minprice; +--------+----------+-------+ | type | variety | price | +--------+----------+-------+ | apple | fuji | 0.24 | | cherry | bing | 2.55 | | orange | valencia | 3.59 | | pear | bartlett | 2.14 | +--------+----------+-------+select type, variety, price from fruits where price = (select min(price) from fruits as f where = fruits.type); +--------+----------+-------+ | type | variety | price | +--------+----------+-------+ | apple | fuji | 0.24 | | orange | valencia | 3.59 | | pear | bartlett | 2.14 | | cherry | bing | 2.55 | +--------+----------+-------+, and so on).

Finding the first several from each group is not possible with that method because aggregate functions only return a single value. Let’s say I want to select the two cheapest fruits from each type.

Here’s a first try: select type, variety, price from fruits where price = (select min(price) from fruits as f where = fruits.type) or price = (select min(price) from fruits as f where = and price (select min(price) from fruits as f2 where f2= fruits.type)); +--------+----------+-------+ | type | variety | price | +--------+----------+-------+ | apple | gala | 2.79 | | apple | fuji | 0.24 | | orange | valencia | 3.59 | | orange | navel | 9.36 | | pear | bradford | 6.05 | | pear | bartlett | 2.14 | | cherry | bing | 2.55 | | cherry | chelan | 6.33 | +--------+----------+-------+ Yuck!

That can be written as a self-join, but it’s just as bad (I leave it as an exercise for the reader).

This gets worse as you go to higher numbers (top 3, top 4…).

Here are some common SQL problems, all of which have related solutions: how do I find the most recent log entry for each program?

How do I find the most popular item from each category? In general, these types of “select the extreme from each group” queries can be solved with the same techniques.

I’ll explain how to do that in this article, including the harder problem of selecting the top N entries, not just the top 1.

This topic is related to numbering rows, which I just wrote about (see my articles about My SQL-specific and generic techniques to assign a number to each row in a group).