Remove Ties from a Top 5 query

[Grd]

Hi there,

I've got the following SQL but I'm am getting more results than I want
because of ties.

SELECT TOP 5 Warehouse, Count(OrderId) AS NumberOrders
FROM tWareHTrans
GROUP BY Warehouse
ORDER BY Count(OrderId) DESC

How do I get rid of the ties - I'm getting more than 5.

Any help greatly appreciated

Tx
Suzanne

 

[Jerry Whittle]

If there is a tie, which one do you not want to see? As they are a tie, is
there any way to distinguish which one not to show with some sort of business
rule?

 

[Grd]

Hi Jerry,

Tx very much for the reply. I just want to see one of the items, I don't
care which one so if there are say 7 tied for 5th place I just want to see
anyone of the 7.

Tx

Suzanne

 

[Klatuu]

Have your query called from another query that will return unique values:

SELECT DISTINCT Warehouse, NumberOrders FROM YourQueryName;

 

[Jerry Whittle]

First you need to create a function in a module. I borrowed the following
from this web page:

http://msaccess.blogspot.com/2005/10/increment-values-in-query.html

global IncrementVariable as Long

function IncrementValues(i) as Long
IncrementVariable = IncrementVariable + 1
IncrementValues = IncrementVariable
end function

Make sure to put the global line at the very top of the module near the
Option Compare Database and Option Explicit lines.

Then this query should work if I got the table and field names correct:

SELECT TOP 5 Warehouse, NumberOrders, TheCounter
FROM (SELECT Warehouse,
Count(OrderId) AS NumberOrders,
IncrementValues(First(NumberField)) as TheCounter
FROM tWareHTrans
GROUP BY Warehouse
ORDER BY Count(OrderId) DESC)
ORDER BY NumberOrders DESC, TheCounter;

 

[John Spencer]

One thing you might try is to add Warehouse to the ORDER By Clause

SELECT TOP 5 Warehouse, Count(OrderId) AS NumberOrders
FROM tWareHTrans
GROUP BY Warehouse
ORDER BY Count(OrderId) DESC, Warehouse

That should do what you say you want, since WareHouse is going to be UNIQUE
based on the query as posted.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Rick Brandt]

Hi there,

I've got the following SQL but I'm am getting more results than I want
because of ties.

SELECT TOP 5 Warehouse, Count(OrderId) AS NumberOrders
FROM tWareHTrans
GROUP BY Warehouse
ORDER BY Count(OrderId) DESC

How do I get rid of the ties - I'm getting more than 5.

Any help greatly appreciated

Just keep adding fields to the ORDER BY clause until the tie is broken.
Including the Priamry Key in the ORDER BY would guarantee that there is
never a tie.

 

[Grd]

Thats perfect.

Tx

One thing you might try is to add Warehouse to the ORDER By Clause

SELECT TOP 5 Warehouse, Count(OrderId) AS NumberOrders
FROM tWareHTrans
GROUP BY Warehouse
ORDER BY Count(OrderId) DESC, Warehouse

That should do what you say you want, since WareHouse is going to be UNIQUE
based on the query as posted.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Grd]

Tx

This works

Just keep adding fields to the ORDER BY clause until the tie is broken.
Including the Priamry Key in the ORDER BY would guarantee that there is
never a tie.

 

[Grd]

John and Rick have the solution that works for me anyway which is to
eliminate the ties by adding more sorting.

Tx for your suggestion

 

[Grd]

Dave - adding another sort fixes the problem

Tx for your help