Ties in access query

[Gordon]

Hi,

When i create a query for the top 3 items for my sales
column i get the ties as well so sometimes I have more
than three.

How can i fix this?

Gordon

 

[Michel Walsh]

Hi,


Add the primary key field(s) at the end of the order by clause. The
primarykey being unique, there will be no more ties.


Hoping it may help,
Vanderghast, Access MVP

 

[Tom Ellison]

If there is a tie, this is an arbitrary way of selecting one and
rejecting another. I suggest you consider seriously whether this is
what you want.

I you award prizes to the top 3 salespersons, for example, and I don't
get a prize even though I tied for 3rd place, just because my primary
key field value is larger, I think I would have good reason to be
unhappy.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Michel Walsh]

Hi,


Use randomly generated autonumber as primary key and thus you can
say randomness has been implied


More seriously, indeed, if the price cannot be split in three, I
would rather not let a database decides who is the winner, and I would list
the 3 records that are TOP 1... a database being a tool to extract data. It
is just too easy to get the same sequence of random numbers over and over,
even a developer with good intention may just produce code that makes no
more sense, at the end, that just using a fixed primary key as tie-breaker.

There are also cases where getting just one, any, of the three is
all what is needed... it is even the default with MS SQL Server, "no
question asked", not even a hint of the existence of a possible "tie". I
assume the original poster was in one of this scenario, since explicitly in
the message, the existence of possible ties was mentioned, and was not
desired/desirable.




Vanderghast, Access MVP

 

[Tom Ellison]

The original post acknowledged there could be a tie, and asked how to
fix it. In some cases, arbitrarily dropping some of the elements that
tied may be appropriate. In other cases, this is very wrong headed.

I'm thankful that SQL standards do not permit the database to
arbitrarily do things like this. We are, of course, forced to
explicitly give the engine a way to perform such arbitrary functions,
such that they are not at all arbitrary to the engine.

In a significant number of cases, saying a horse that tied for "show"
has finished out of the money will cause serious repercussions. It
was with that thought I suggested the person writing the application
needs to give this due consideration before accepting an arbitrary
solution.

One very good answer to "how do I fix this" is that there's nothing to
fix. When there's a tie for 3rd place, then there must be more than
three horses that finished in the money.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Gordon]

That worked perfectly,

I don't know why but it works

THanks a lot

Gordon