Archive for September 14th, 2009
Answering questions asked on the site.
Hello, thanks for your great post about advanced row sampling, it helped me a lot.
I have a question.
I am using your SQL query to show a portfolio of products and these products have a certain integer quality level (the lower, the better).
What if I want to show the best 3 products of each category?
Just a quick reminder: the article mentioned describes a query to emulate
ROW_NUMBER() in MySQL. Since MySQL does not support analytic (window) functions natively, we had to do some kind of a trick to mimic their behaviour.
The original article described a query to obtain 15 first
id‘s for each
grouper. The idea behind this query was quite simple:
- Take a list of groupers using
- For each grouper, find the 15th
LIMITin a correlated subquery. We have now a set of
(grouper, id15)pairs: for any given
grouperwe have the
idof the 15th row (ordered by
- Join this resultset with the main table so that for each
id‘s less or equal to the 15th
idare selected. We then will have 15 lowest
id‘s for each
grouperthat is exactly what we were after.
ROW_NUMBER() OVER (PARTITION BY grouper ORDER BY id): a function with a single
PARTITION BY expression and a single
ORDER BY expression.
But in your case there are multiple
ORDER BY expressions. It may be not so obvious (since the question mentions only one ordering criterion, namely,
quality). However, the solution above relied on the fact that the field in
ORDER BY was unique, so we now that there are exactly 15 rows with
id less or equal to the
id returned with
LIMIT 15. Since this is most probably not the case with quality, we should include
id into the
ORDER BY clause so that it makes the ordering conidition unique.
A subquery in MySQL, however, can only return a single value.
To work around this, we should use two subqueries that will return us a single column in an
ORDER BY condition.
Let’s create a sample table:
Read the rest of this entry »