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

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!!

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

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

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?

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

o rly?

would you mind showing us your solution?

uhh, why?
I just did exactly what you told me to do.

to confirm

I used LIMIT.

Can you please help me with the second question?

aha

sure -- use a self-join