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
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 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.
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
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?