Bug? Limiting and ordering by datefield...

[Per-Olov Jernberg]

Ok, just found this weird bug/feature when i was testing another thing

I have one table, named "Test" with two columns, one named "Created"
(Date-time column) and one dummy-column named "Biff" (Text-column) with
the following data:

Created Biff
2004-01-01 nothing
2004-02-01 nothing
2004-03-03 nothing
2004-03-03 nothing
2004-05-05 nothing

Then if i run this query:

SELECT TOP 3 Created FROM Test ORDER BY Created;

You'd expect 3 rows retured, but noooo, it's 4 rows returned:

Created
2004-01-01
2004-02-01
2004-03-03
2004-03-03

Well well, nothing serious happened but can someone explain this, i'm
guessing it's a bug since "TOP 3" should filter the results AFTER it's
rearranged with "ORDER BY", right?

Would be nice to see if anyone else is experiencing the same bug?

 

[Michel Walsh]

Hi,


No, it is not a bug. Since there is ex-equo for the last values, all the
records qualifying are retrieved. As example, if it is about time for a
race, and two racers have the same time, in third position, who would you
disqualify? Won't you make the two racers in third position? We call that
behavior "TOP n WITH TIES", which is the default, with Jet. If you wish just
n, not more, (but maybe less), add the primary key value in the ORDER BY


SELECT TOP 3 f1, f2, f3, f4
FROM myTable
ORDER BY f1 DESC, pk

where I assume pk is the field name for the primary key. The primary key
being unique, it acts like a tie-breaker, so you never got more than, here,
3 records.



Hoping it may help,
Vanderghast, Access MVP

 

[Per-Olov Jernberg]

Ok, that explains it

I always thought that TOP N equals LIMIT N in other databases where it
just meant "N and maybe less"

Thanks alot for the quick answer!

 

[Jeff Boyce]

I'm suspecting the following:

If you handed a list of those dates to someone and asked them to pick all
the rows with the three most recent dates, would you expect to get two
mentions of 2004-03-03 or one (there are TWO rows with that date)? I'd
expect them two find two FOR THAT DATE.

One interpretation would return 20040505 and 20040303 ... just two, not
three!

Another interpretation would return 20040505, 20040303, and 20040201 (the
three dates most recent).

What you got was 20040505, 20040303, 20040303 and 20040201 (the three most
recent dates, all instances). If you'd had five rows with 20040505 and five
with 0303 and five with 0201, you'd get 15 rows returned!

If you want the unique three most recent dates, use the TOP 3, then change
the UniqueValues property to Yes.