CODEWITHSUNDEEP
sqlms-access-2013
Morgan
Morgan

Reputation: 87

How to Adjust Query to Show Top 5 When Grouped and Ordered

I have a query that i am trying to run which counts the recurrence of part_number, groups it, and displays the output.

I would like to only display the top 5 entries (rows) in descending value.

Here is my code for reference.

SELECT TOP 5 
part_log.part_number,
COUNT(part_log.part_number) AS CountOfpart_number
FROM part_log
GROUP BY part_log.part_number
ORDER BY Count(part_log.part_number) DESC;

The problem is for example, if I have 8 part numbers with a variance in values, but the 5th row value is the same as the 6th, 7th, and 8th, then it will spit the below data out instead of just cutting it off at the 5th row.

p/n: 5 occ:10
p/n: 6 occ:7
p/n: 2 occ:5
p/n: 3 occ:4
p/n: 1 occ:2
p/n: 4 occ:2
p/n: 7 occ:2
p/n: 8 occ:2

I don't have a preference for which part number gets spit out from the ones with the same value, I simply only want 5 rows to be retrieved.

Removing the ORDER BY function gives me only 5 rows, but omits the descending values that I need.

Any help on this would be greatly appreciated.

Upvotes: 0

Views: 39

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

MS Access treats TOP as TOP WITH TIES. That is, it continues to return equal values when there are ties.

The simple solution is to add an additional key. In this case, the obvious key to add is the GROUP BY key -- because you know that will be unique on each row:

SELECT TOP 5 pl.part_number,
       COUNT(*) AS CountOfpart_number
FROM part_log as pl
GROUP BY pl.part_number
ORDER BY Count(*) DESC, pl.part_number;

Upvotes: 1

GMB
GMB

Reputation: 222582

How about adding a second criteria to the ORDER BY clause?

ORDER BY Count(part_log.part_number) DESC, part_log.part_number;

Since you are grouping by part_numbers, the values are guaranteed to be unique in the resultset, which makes this column a perfect candidate to break the ties.

Upvotes: 1

Related Questions