Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2011
    Posts
    4

    Unanswered: Help: Using MAX and COUNT in query

    Hi,
    I don't understand why my sql query won't work......
    This is one of my last homework problems....
    Help will be greatly appreciated..

    I'm given 3 different tables.
    Table1 Name: user
    with Columns: uid, first_name, last_name, birthday_date, sex, locale
    Table2 Name: friend
    with Columns: uid1, uid2
    Table3 Name: page_fan
    with Columns: uid, page_id, type, created_time

    The coolest page in the database is the one with the most fans. Write an SQL query to find the page_id and the number of fans (as a column called Fans) for the coolest page.

    This is the query I wrote.
    SELECT page_id, MAX(COUNT(uid)) AS Fans
    FROM page_fan
    GROUP BY page_id

    I don't understand why using MAX with COUNT as parameter doesn't work...
    It gives me Invalid use of group function error.

    Also I need help with another problem.
    I don't even know where to start with this question...

    15.Sarah Davis (uid = 20168961) wants to find her closest friend. She defines this as the friend who has fanned the maximum number of pages among the pages that Sarah has fanned. Write an SQL query to find the first name and last name of Sarah’s closest friend according to this definition. Your query should work with any other user if we replace Sarah’s uid with another uid.

    I would really appreciate any advice.
    Thanks

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I think MAX is not really what you are looking for per page? You are looking for the number of DISTINCT users per page. This result set will not return a single row with your answer but if you order the result set you will get your answer.

    For obvious reasons I do not want to do your assignment for you but I am prepared to point you in the right direction.

    As to your second question, Sarah has associated page_id. In this question you will need to SELF JOIN the table to find all other uid that have links to similar pages. We need to count these and the one with the largest similar pages will be her best friend. At least that is my interpretation on this question.

    Good luck with the assignment!!
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    May 2008
    Posts
    7
    Find below the query to get the right result, as you are missing on count path that i am trying to correct.

    SELECT page_id, MAX(select COUNT(uid) from page_fan GROUP BY page_id) AS Fans
    FROM page_fan
    GROUP BY page_id

    Thanks,
    Amol Verma
    MCTS SQL Server 2005, OCA/OCP Oracle 9i/10g
    Quality Assurance Central
    QAC Forums

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by the_amol View Post
    Find below the query to get the right result
    sorry, that does not produce the correct result

    just do the GROUP BY query in post #1, but without the MAX around the count, and then add an ORDER BY clause to sort the results so that the page with the most fans comes first, and then find some way to limit the results to just the first row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Be aware that this is an assignment and you should work through this yourself. Also "The coolest page in the database is the one with the most fans". This is not simply the number of page accesses. If one person accesses the page 1000 times but 1000 persons access the page once, which is the most popular page?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Apr 2011
    Posts
    4
    Thank you for all replies.
    I was able to solve the first question.

    But can you please give me some more advice on second question?

    I get the idea of using SELF JOIN. But I'm kinda stuck how to write the nested queries.....

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by noneed52 View Post
    I was able to solve the first question.
    o rly?

    would you mind showing us your solution?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2011
    Posts
    4
    Quote Originally Posted by r937 View Post
    o rly?

    would you mind showing us your solution?
    uhh, why?
    I just did exactly what you told me to do.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by noneed52 View Post
    uhh, why?
    to confirm
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2011
    Posts
    4
    I used LIMIT.

    Can you please help me with the second question?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by noneed52 View Post
    I used LIMIT.
    aha

    Quote Originally Posted by noneed52 View Post
    Can you please help me with the second question?
    sure -- use a self-join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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