On Mon, Jan 09, 2006 at 01:38:26PM -0600, Sean McMains wrote:
} I have a table with some data structured something like this:
} title (varchar)
} pages (int)
} completed_on (date)
} While I can find the shortest book completed in each year with
} something like this:
} select min( pages ), year from book group by year
} I would also like to get the title of the shortest book in each year.
} Is there any way to do this by modifying the previous query to pull
} that as well in a single pass? The only approach I can think of is to
} iterate through the results of the above query, doing another query
} for each shortest book I want to get all the details for, which seems
} a mite inefficient.
You should realize that if any given year has more than one book of the
minimal number of pages for that year you will get all of them. I will
assume that you have a year field, which you do not mention. That said:
FROM book b JOIN (
SELECT MIN(pages), year
GROUP BY year
) m ON m.year = b.year AND m.pages = b.pages
This requires an RDBMS that supports nested queries. PostgreSQL, as well
essentially all major commercial RDBMSs do. I have heard that MySQL
does not, though that may have changed by now. This also requires an
that understands the JOIN keyword, though it can be rephrased to use a
WHERE clause instead.
} Thanks, SQL mavens!