Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2009
    Posts
    6

    Unanswered: Finding a most occuring item (HELP)

    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?
    Last edited by dritzthevampyr; 10-08-09 at 08:39.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2009
    Posts
    6
    void void void
    Last edited by dritzthevampyr; 10-08-09 at 08:40.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2009
    Posts
    6
    void void void
    Last edited by dritzthevampyr; 10-08-09 at 08:40.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2009
    Posts
    6
    void void void
    Last edited by dritzthevampyr; 10-08-09 at 19:59.

  8. #8
    Join Date
    Oct 2009
    Posts
    6
    void void void
    Last edited by dritzthevampyr; 10-08-09 at 20:00.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    we're all still asleep, keep your pants on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2009
    Posts
    6
    void void void

    This thread is done. Not much assistance was provided. Solution found in a different forum.
    Last edited by dritzthevampyr; 10-08-09 at 20:02.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •