SQL select rows with max repetition betwen two dates

I have three tables Books, Debit and Client.

1. Books (Id, Title, ...)
2. Debit (BooksID, ClientId, BorrowingDate,....)
3. Client (Id, Name, ...)

I need SQL query that开发者_如何学C will return a book that is most times rented out, between two dates. Dates will be passed as parameters to query.

How about this?

SELECT * FROM Books INNER JOIN Debit ON Books.Id = Debit.BooksID WHERE BorrowingDate >= @StartDate AND BorrowingDate <= @EndDate

If you edit in an example of what you would like the output to look like I can redefine the above query to help you get what you want.

And for the record, Adrian is right; it is generally better to make an attempt yourself and come to us with a problem, rather than asking SO to solve the problem for you. But hopefully what I have here will get you started. Welcome to SO! :)

Edit

It also just occurred to me that you might be looking for the book which was rented out most number of times between your two dates... If so, try this:

SELECT TOP 1 Books.* FROM (
    SELECT COUNT(*) AS DebitCount, BooksID FROM Debit WHERE BorrowingDate >= @StartDate AND BorrowingDate <= @EndDate GROUP BY BooksID
) Debits INNER JOIN Books ON Books.ID = Debits.BooksID
ORDER BY DebitCount DESC

Hope that helps!

The query...

SELECT TOP 1 *
FROM (
    SELECT BooksID, COUNT(*) AS C
    FROM Debit
    WHERE BorrowingDate BETWEEN @min_date and @max_date
    GROUP BY BooksID
)
ORDER BY C DESC

...will yield one row containing the BooksID and number of rents of the book that has been rented most times between @min_date and @max_date.

You can later easily JOIN with Books if you need more than just BooksID.