Hi Behrang,
Behrang Saeedzadeh wrote:
Hi Baron,
Thanks. That that worked great. Is it possible to insert an empty row
after the books by the same author?
-Behi
On 4/12/07, Baron Schwartz <[EMAIL PROTECTED]> wrote:
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."
Please remember to reply to the list so others can read and benefit from answers to
your questions. Also, though I don't care tremendously one way or another, many people
think it's good form to place your response after the message instead of before (I tend
to follow the pattern set by the first response -- I don't care either way as long as
it's not back-and-forth in different styles, which makes it impossible to figure out
the sequence of replies).
To answer your second question, I can't think of a way to do this. It sounds like
you're getting into the realm of formatting something for visual display, which is not
what SQL is best used for in my opinion.
Regards
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
