Find Most Frequent record occurances

  • Thread starter Thread starter Rhonda
  • Start date Start date
R

Rhonda

What would be the most efficient way to select the records in a query/record
set that appear most frequently? Maybe the Top -n- most frequent.
Any advice welcomed.
Thanks
 
I would use a query something like the following.

SELECT *
FROM TableA
WHERE FieldA IN
(SELECT Top 50 FieldA
FROM TableA
GROUP BY FieldA
ORDER BY Count(FieldA) Desc)
 
Thanks
I have tried your suggestion, and several permutations of it without
success. Mine looks like this:
SELECT *
FROM qryTempRptQry
WHERE JobLabel IN(SELECT TOP 10 JobLabel
FROM qryTempRptQry
GROUP BY JobLabel
ORDER BY Count(JobLabel) Desc);

My results seem not be in order of Count(JobLabel) Desc, they are Grouped
though. Under my actual Group, there is a row for each item, but They read
like these, with the Count not descending as expected:

JobLabel1 40 items
JobLabel12 10 items
JobLabel32 31 items
JobLabel7 6 items and so on.

Does the 50 in your original example mean the top 50 in the record stack,
regardless of field, or otherwise?
I hope this is clear enough for you to kind of see what I'm trying to
accomplish.
Thanks
 
Also,
If you have any suggestions on simply getting a Count of items under each of
my JobLabel groups. Somethong like Select *, Count(qryTempRptQry.JobLabel)
AS Quantity. I get an error with every variation I have tried.
Thanks
 
This query gets the top 10 items by count. If you want to order these by count,
you will need to do some further work

SELECT <List the fields you want to display>
FROM qryTempRptQry
WHERE JobLabel IN (SELECT TOP 10 JobLabel
FROM qryTempRptQry
GROUP BY JobLabel
ORDER BY Count(JobLabel) Desc)
GROUP BY <Your List of Fields>
ORDER BY Count(JobLabel) Desc

Although further thought leads me to believe that you could probably use the simpler:

SELECT TOP 10 <List of fields>
FROM qryTempRptQry
GROUP BY <List of Fields>
ORDER BY Count(JobLabel) Desc
 
Back
Top