How to write the T-SQL code for a query

Question

I need to use t-sql to query two tables. The first table is Books. The second table is Authors. For each Book record there could be multiple child Author records. I want to write a query that only returns the first Author record found for the current Book record. There are hundreds of thousands of records in the tables so I need the query to be efficient.

select a.FirstName, a.LastName, b.BookName
from Books b
left join 
(
    select TOP 1 t.BookID, t.FirstName, t.LastName 
    from Authors t
) a 
    on a.BookID = b.BookID
where b.BookClassification = 2

This query is not right. I only want to select the top 1 record in the Authors which match the BookID. How can I get the results I am looking for?

There doesn't have to be any criteria used for the author selected as long as it has the same BookID — user31673, Apr 10 '12 at 18:15
Can you not use a subquery? Also, an order by is missing, the joined query/table (a) is always returning the same row. — JJ_Jason, Apr 10 '12 at 18:17
I don't know how to structure the t-sql to get the results I want. The sql above is wrong. — user31673, Apr 10 '12 at 18:18
Out of time to write a full answer for your case. Take a look here: http://stackoverflow.com/a/7745635/570191. Choose your "top author" criteria (seriously, you should have one) and adapt to your needs. — Adriano Carneiro, Apr 10 '12 at 18:22

RBarryYoung · Accepted Answer · 2012-04-10T19:43:43.957

6

You were close:

select a.FirstName, a.LastName, b.BookName
from Books b
outer apply 
(
    select TOP 1 t.BookID, t.FirstName, t.LastName 
    from Authors t
    WHERE t.BookID = b.BookID
    -- uncomment the next line to control which author to prefer
    -- ORDER BY t.<someColumn>...
) a 
where b.BookClassification = 2

Though it seems odd to me that Authors would be a child of Books... :)

edited Apr 10 '12 at 19:43

answered Apr 10 '12 at 18:21

RBarryYoung

55,398
14
96
137

paparazzo · Answer 2 · 2012-04-10T18:55:53.610

See if this is more efficient. By looking for min(authorID) just once you might get better performance.

select author.FirstName, author.LastName, author.BookName    
from Books with (nolock) 
join 
(   select min(authorID) as authorID, bookID 
    from Authors with (nolock) 
    group by bookID 
)   as Author1
  on Author1.authorID = Books.authorID 
join Authors with (no lock) 
 on  Authors.authorID = Author1.authorID 
 and Authors.bookID = Author1.bookID
where Books.BookClassification = 2

score 0 · Answer 3 · answered Apr 10 '12 at 19:10

In the spirit of TIMTOWTDI.

You can use a CTE, a fancy subquery but helpful if the subquery is used more than once. And one the rank functions, row_number().

with bookAuthors as ( 
  select a.FirstName, a.LastName, b.BookName, BookClassification, 
    row_number() over(partition by b.BookName order by a.lastName ) as rank
  from Books b
  left join Authors a
    on a.BookID = b.BookID

)

select a.FirstName, a.LastName, b.BookName
from bookAuthors 
where rank = 1
  and BookClassification = 2

How to write the T-SQL code for a query

3 Answers3