From: | Andrew Gould <andrewgould(at)yahoo(dot)com> |
---|---|
To: | Adam <adamcrume(at)hotmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: count of occurences |
Date: | 2001-09-14 18:40:00 |
Message-ID: | 20010914184000.45801.qmail@web13407.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
You're just missing 'group by', and a little
simplicity.
Try this:
select job_num, count(job_num) as frequency
from search_records
group by job_num
order by frequency desc
limit 10;
Have fun,
Andrew Gould
--- Adam <adamcrume(at)hotmail(dot)com> wrote:
> I help run a job database and have a table of search
> records. I want
> a query that will return the top 10 jobs by search
> frequency. I'm
> familiar with ORDER BY and LIMIT, so I basically
> need this:
>
> Given a table search_records:
> job_num
> -------
> 1
> 2
> 2
> 3
> 4
> 4
> 4
>
> I want a query that will return:
> job_num | count
> --------+------
> 1 |1
> 2 |2
> 3 |1
> 4 |3
>
> I tried
>
> select distinct job_num, (select count(*) from
> search_records j where
> j.job_num=k.job_num) from search_records k
>
> but it is horribly slow (it takes several minutes on
> a table of about
> 25k rows!). I assume it scans the entire table for
> every job_num in
> order to count the number of occurences of that
> job_num, taking order
> n^2 time. Since I can easily use job_num as an
> index (being integers
> from 0 to roughly 400 so far) I could just do a
> "select * from
> search_records" and do the counting in PHP (our HTML
> pre-processor) in
> order n time. However, I don't know how to do an
> order n*log(n) sort
> in PHP, just n^2, so there would still be an
> efficiency problem.
> I have Postgresql 7.0.3.
> Help is of course greatly appreciated.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
__________________________________________________
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Eckermann | 2001-09-14 18:56:59 | Re: Can I insert two different variables from form into different tables using one SQL query ? |
Previous Message | Doug McNaught | 2001-09-14 17:28:15 | Re: How to implement transaction in plpgsql? |
From | Date | Subject | |
---|---|---|---|
Next Message | Martín Marqués | 2001-09-14 20:05:41 | chunk size problem |
Previous Message | Rene Pijlman | 2001-09-14 18:38:01 | Re: querying system catalogs to extract foreign keys |