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@hotmail.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/
