> I tried
>
> select distinct job_num, (select count(*) from search_records j where
> j.job_num=k.job_num) from search_records k
can't you just do:
select job_num, count(job_num) from search_records group by job_num
order by job_num
http://www.postgresql.org/idocs/index.php?queries.html#QUERIE
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 <[EMAIL PROTECTED]> wrote:
> I help run a job database and have a table of se
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_nu
Sorry about the size of this message!, it covers several optimisation
areas.
Yes we are talking about a limited situation of ORDER BY (that does not
match the GROUP BY order) plus LIMIT, but one that is easy to identify.
It also has the advantage that the number to be LIMITed will 9 times out
of
HACKERS: see the end of this message about a possible optimisation for
ORDER BY+LIMIT cases (the normal use of LIMIT?)
Adam 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
