Re: SQL TOP 50,000 Help

On Fri, 9 Feb 2007 10:59:00 -0800, Klatuu
<Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Sorry, Bob, I don't see the point of all this. The OP is trying to return
the TOP 50,000 records based on some criteria which to work requires the
ORDER BY.

That is the whole beauty of a hash function ... no ORDER BY is
necessary! When I worked on the Swiss census in 2000, we had
partitioned tables with > 12 million rows (in an Oracle database, not
Access <g>) and our nightly batch programs had to select exactly one
of eight possible partitions because they ran in parallel and couldn't
be allowed to process the same data. It was also important WRT load
balancing. The Oracle built-in hash function works VERY fast
(actually, I believe it resides in the package DBMS_UTIL, but I could
be mistaken). Unfortunately, Access has no such built in function, but
using MOD etc. on the primary key, you can achieve much the same
results.

As to your concerns WRT to the text export, it is really much faster,
and also gives you a smaller file.

The trouble with recommending TOP with ORDER BY is when you start to
have more than 1,000,000 and not just 130,000 rows. It is OK for
quick-and-dirty things, but I try to avoid it at all cost. It is a bad
habit, and most other RDBMS don't even offer it.

--
Bob Hairgrove
NoSpamPlease@xxxxxxxx
.