MySQL Forums
Forum List  »  General

group by / order by problem (how to sort before group)
Posted by: Markus Krebs
Date: January 15, 2007 11:39AM

Hi all,

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.

example:

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?

like this:

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.

regards,
Markus

Options: ReplyQuote


Subject
Written By
Posted
group by / order by problem (how to sort before group)
January 15, 2007 11:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.