Skip to main content
Known Participant
Aug 17, 2006 8:52 PM UTC
Answered

distinct group of entries

i'm trying to retrieve a list of books, one book from each category, based on popularity...and i'm not sure how to go about doing it.

example:
Categories:
E - Educational (500 records)
M - Mystery (500 records)
R - Romance (500 records)
A - Adventure (500 records)

so in order to get the list of books with the most reviews:
select *
from books
order by reviews desc

..this will return 2000 records of course...now if i want only the most reviewed book from each category, how would i go about doing that?

thanks! 🙂
This topic is closed to new replies. Start a new post to keep the conversation going.
Kronin555Correct answer
Participating Frequently
Aug 17, 2006 10:29 PM UTC
<cfquery name="getHighestRatedInEachCategory">
select
b.bookid, b.categoryid, b.bookname, b.reviews
from
books b,
(select categoryid, max(reviews) as reviews from books group by categoryid) b2
where b.categoryid = b2.categoryid and b.reviews = b2.reviews
</cfquery>

8 replies

Known Participant
Aug 17, 2006 10:35 PM UTC
thank you!

edit:
i can now see how Dan's example makes sense, thanks Kronin :)
Translate
Kronin555Correct answer
Participating Frequently
Aug 17, 2006 10:29 PM UTC
<cfquery name="getHighestRatedInEachCategory">
select
b.bookid, b.categoryid, b.bookname, b.reviews
from
books b,
(select categoryid, max(reviews) as reviews from books group by categoryid) b2
where b.categoryid = b2.categoryid and b.reviews = b2.reviews
</cfquery>
Translate
Known Participant
Aug 17, 2006 10:25 PM UTC
thanks for the reply Dan, but I can't make heads or tails or your example.

"TestDateTime" seems to be a key field, but I can't see how it coresponds to the fields I'm using..
Translate
Known Participant
Aug 17, 2006 9:59 PM UTC
heh thanks for the reply...i'm trying to avoid sub queries as yes, they are inefficient, and given that there is closer to 400,000 records not 2000, it will cripple DB performance.

i was thinking of retrieiving 500~ records then doing a QoQ, or simply filter it in the output..i'm hoping there is a more efficient way of going about it within the query itself.
Translate
Known Participant
Aug 17, 2006 8:59 PM UTC
well based on the query example, yes ;)

select
bookid, categoryid, bookname, reviews
from
books
order by
reviews desc
Translate
Aug 17, 2006 9:19 PM UTC
This is probably horribly inefficient, but should work.

Translate
Participating Frequently
Aug 17, 2006 8:57 PM UTC
You need more information, such as is there a reviews column in books, etc.

Phil
Translate