I have a table with some data structured something like this:
book
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.
Thanks, SQL mavens!
Sean
On Mon, Jan 09, 2006 at 01:38:26PM -0600, Sean McMains wrote:
} I have a table with some data structured something like this:
}
} book
} ----
} 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
also
assume that you have a year field, which you do not mention. That said:
SELECT b.*
FROM book b JOIN (
SELECT MIN(pages), year
FROM book
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
as
essentially all major commercial RDBMSs do. I have heard that MySQL
still
does not, though that may have changed by now. This also requires an
RDBMS
that understands the JOIN keyword, though it can be rephrased to use a
WHERE clause instead.
} Thanks, SQL mavens!
} Sean
–Greg