Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Exir
Posting Yak Master
151 Posts |
Posted - 2013-03-05 : 13:21:22
|
Hi There is some tables :Author ( AuthorID , AuthorFirstName , AuthorLastName )BookTitle ( ISBN_Number , Title , PublisherID , Date_of_Published , Pages , Price , Comment )Publisher ( PublisherID , PublisherName )Category ( CategoryID , CategoryDescription )Book_Author ( ISBN_Number , AuthorID )Book_Category ( ISBN_Number,CategoryID )I want to select the name of the Author who has the minimum compliation.Please help me with the query |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-05 : 13:34:26
|
[code]SELECT TOP (1) WITH TIES a.AuthorID, a.AuthorFirstName, a.AuthorLastName, COUNT(*) AS CompilationsFROM Author a INNER JOIN Book_Author b ON a.AuthorID = b.AuthorIDGROUP BY a.AuthorID, a.AuthorFirstName, a.AuthorLastNameORDER BY Compilations ASC;[/code]Can there be authors with no publications at all? If there are, are those the ones you want to pick? |
|
|
Exir
Posting Yak Master
151 Posts |
Posted - 2013-03-05 : 13:47:02
|
Thank you so much for your answerquote: Originally posted by James KCan there be authors with no publications at all? If there are, are those the ones you want to pick?
yes, if there is some authors with no publications, those authors should be chosen |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-05 : 14:12:53
|
Ok - make the changes shown in redSELECT TOP (1) WITH TIES a.AuthorID, a.AuthorFirstName, a.AuthorLastName, COUNT(b.ISBN_Number) AS CompilationsFROM Author a LEFT JOIN Book_Author b ON a.AuthorID = b.AuthorIDGROUP BY a.AuthorID, a.AuthorFirstName, a.AuthorLastNameORDER BY Compilations ASC; |
|
|
|
|
|
|
|