List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:April 12 2007 12:13pm
Subject:Re: Query Question
View as plain text  
Behrang Saeedzadeh wrote:
> Hi,
> 
> Suppose that there are two tables book and author:
> 
> book
> --------
> id
> title
> author_id
> 
> author
> ---------
> od
> title
> 
> I want a query that returns all the books, but if there are more than
> 3 books with the same author_id, only 3 should be returned. For
> example if this is the contents of the book table:
> 
> (1, "Book 1", 10)
> (2, "Book 2", 10)
> (3, "Book 3", 10)
> (4, "Book 4", 10)
> (5, "Book 1", 11)
> 
> these rows should be returned:
> 
> (1, "Book 1", 10)
> (2, "Book 2", 10)
> (3, "Book 3", 10)
> (5, "Book 1", 11)

Try this:

select id, title, author_id
from books
where (
    select count(*) from books as b
    where b.author_id = books.author_id and b.id < books.id
) <= 3;

In English, "select books where there aren't more than three lower-numbered books by 
the same author."

Baron
Thread
Query QuestionBehrang Saeedzadeh12 Apr
  • Re: Query QuestionBaron Schwartz12 Apr
Re: Query QuestionBaron Schwartz12 Apr
  • Re: Query QuestionBehrang Saeedzadeh12 Apr