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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
