Finding a most occuring item (HELP)

dritzthevampyr · #1 (**permalink**) 10-07-09, 23:04

Hello,

I am trying to work on my SQL database that I've set up that is supposed to be exampling a library system.

Document(document#, document_title, document_type, document_descriptors)

Borrowing(reader#, document#, copy#, date_borrowed, date_returned)

I'm supposed to put through a query that returns the document(s) that is(are) the most borrowed. I would have to compare the copy#'s to see what document_title has the most copy# that have a copy_status of 'borrowed'. How exactly would I do this in SQL?

So far I have the following:

Code:

SELECT D.document_title, COUNT(*) as "Times borrowed"
FROM Document D, Borrowing B
WHERE D.`document#` = B.`document#`
GROUP BY D.document_title
ORDER BY COUNT(*) DESC LIMIT 1

This gives me the highest borrowed document, but if there were two I wouldn't see that because of the limit. Is there anyway to alter this to show which document(s) is(are) the most borrowed?

r937 · #2 (**permalink**) 10-07-09, 23:20

Quote:

Originally Posted by dritzthevampyr

I'm supposed to put through a query that returns the document that is the most borrowed.

having trouble with your homework assignment?

do not just ask for the answer, we will not hand it to you

~you~ must do the work to come up with a solution

we will only guide you and help you to understand

what have you tried so far?

dritzthevampyr · #3 (**permalink**) 10-07-09, 23:27

void void void

r937 · #4 (**permalink**) 10-07-09, 23:31

that's a good observation -- LIMIT will not show ties properly, so you shouldn't be using LIMIT

you've correctly figured out that GROUP BY is needed, but i don't understand why you have three tables in your FROM clause

dritzthevampyr · #5 (**permalink**) 10-07-09, 23:43

void void void

r937 · #6 (**permalink**) 10-08-09, 00:14

you're not on the right track yet

the status column shows you only the current status of a copy

the question asks which document is most borrowed

you cannot tell how often a copy has been borrowed by looking at its current status