group by / order by problem (how to sort before group)
Posted by: Markus Krebs
Date: January 15, 2007 11:39AM
i have the following problem, which would be best described in one sentence by "how can i first sort, then group in a query?":
I have stored book titles in a table, together with their respective publisher-id and availability date.
Now i wanted to generate a top10 table, where the newest 10 book titles of the publishers are stored. every publishers id should be unique in that table, which should get updated regularly.
so my problem is, that if i select all titles and group them by publishers id, sort them by publishing date (descending) and limit the result set by 10, i never get the latest titles per publisher id (10 maximum) as result.
the main sort criteria has to be the publishing date but the group by clause comes first in the query (by design of SQL), so i can only sort by the publishing dates of the aggregated results.
select id,title from books group by publisher_id order by publish_date limit 10;
is there any chance to work the desired result out in ONE query or do i have to distinctly select only the publishers id and sort by the publishing date in a (performance costly) subselect?
select id,title from books where publisher_id in (select distinct publisher_id from books order by publish_date desc limit 10);
well, almost anyone would advise me to write a flat distinct query but this is an abstraction of the real query, which uses some joins in the outer select and is a bit more complicated that the "bookstore" example i used to explain the problem.