How about:
SELECT pages FROM <table> GROUP BY pages ORDER BY count(*) DESC LIMIT 5;
and
SELECT users FROM <table> GROUP BY users ORDER BY count(*) DESC LIMIT 5;
Make sure the table is index on pages and on users.
JLL
Paulo Jan wrote:
>
> Hi all:
>
> Let's say I have a table that keeps track of the pages that an user has
> seen in my website, something like:
>
> Users | Pages
> -----------------------------
> pepe | index.html
> johnsen | about.html
> paco | about.html
> paco | index.html
> pepe | download.html
>
> It's easy to see the last 5, or 10, or 15 pages that an user has seen,
> or the last users that have viewed a page. But how can I find out the 5
> most seen pages, or the 5 most frequent users? My first thought (to find
> the most viewed pages) was to:
>
> 1) "SELECT DISTINCT pages FROM <table>"
> 2) For each page, "SELECT count(users) FROM <table> WHERE
> pages='<page>'"
> 3) Find out the 5 pages that have returned the highest counts.
>
> At which point I imagined the perfomance problems that all the above
> would cause in a dynamic page (PHP) and said to myself "ew!!".
> Is there any other way? Any built-in function in Postgres that can do
> this, perhaps?
> (I used pageviews just as an example; what I have is a number of
> similar situations where I need to find out this kind of data, and in
> all of them said data is updated dynamically and needs to be displayed
> in real time).
>
> Paulo Jan.
> DDnet.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster