List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:April 12 2007 2:47pm
Subject:Re: Query Question
View as plain text  
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 <baron@stripped> 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
Thread
Query QuestionBehrang Saeedzadeh12 Apr
  • Re: Query QuestionBaron Schwartz12 Apr
Re: Query QuestionBaron Schwartz12 Apr
  • Re: Query QuestionBehrang Saeedzadeh12 Apr