obtaining the N most common records

# Thread: obtaining the N most common records

1. Junior Member
Join Date
Dec 1969
Posts
26

## obtaining the N most common records

Can anyone think of a fast/efficient way to collect the top N most common results?<BR><BR>an example could be a survey of peoples favorite food.. gleaning the top 5 most favorite foods..<BR><BR>thanks for your time,<BR>Jason

2. Senior Member
Join Date
Dec 1969
Posts
973

## TOP

Assuming you are using SQL<BR><BR>You can use a rating system and populate a field with the rating value...<BR>To query the TOP 5 would simply be...<BR><BR>SELECT TOP 5 foodRating from tbl_food ...... would give you the top 5...<BR><BR>or if you are using Access.... sort on the foodRating as Desc and take the first 5 records.<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
607

## Is it????

I thought that "TOP" would only give you the top "n" records from the database. <BR><BR>But the records would be scattered in the table. Top 5 people may rate 5 different food items as favorites and the 6th person may again put in say "chinese" as favorite. Now out of these 6 people Chinese Food had the most votes but since the second occurence of it is on 6th position, it wont be picked up by the sql if you do "select top 5..."<BR><BR>...how does this solve the problem ?

4. Senior Member
Join Date
Dec 1969
Posts
973

## RE: Is it????

ok... that won&#039;t work..... do you have the table structure? How many food types do you have?<BR>

5. Junior Member
Join Date
Dec 1969
Posts
26

## RE: Is it????

vineet is correct.. TOP will tell me the top 5 numerical values in a field.. but i really need the top 5 most frequent responses, which has nothing to do with the relative numerical value of each response.. the solution i am currently using is:<BR><BR>i have 2 tables:<BR>td_wr with <BR>wr_id<BR>wr_type<BR><BR>and td_wr_eqpinv_list with<BR>wr_id<BR>item<BR><BR>i need the top 25 items of a certain wr type..<BR><BR>"SELECT td_wr_eqpinv_list.item, count(item) as itemcount FROM td_wr_eqpinv_list inner join td_wr on td_wr_eqpinv_list.wr_id = td_wr.td_wr WHERE tx_wr_type_code=&#039;c&#039; group by td_wr_eqpinv_list.item order by count(tx_item_id) desc"<BR><BR>I came up with this solution after i posted the first message.. does anyone know a quicker way? or is this about as good as it gets?<BR><BR>thanks,<BR>Jason

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•