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
