The TOP N clause in VFP 8 doesn't work reliably unless you put in more than one sort order. ( and even then it's a bit funky)
Consider the CUSTOMER table.
USE HOME(2)+"NORTHWIND\customers"
SELECT TOP 5 * from customers ORDER BY 1
SELECT TOP 5 * from customers ORDER BY 1
This gives you only the first 5 records.
But if you add another table into it.
USE HOME(2)+"NORTHWIND\orders" IN 0
SELECT TOP 5 * from customers, orders WHERE customers.customerid=orders.customerid ORDER BY 2
SELECT TOP 5 * from customers, orders WHERE customers.customerid=orders.customerid ORDER BY 2
This returns 6 entries.
As soon as you combine two tables and pull data from both, then the TOP n doesn't return only the top 5 entries.
Confused? Yeah - it's a PITA.
It's an aberration that you can't really work with. As a result TOP N isn't something you can always rely on.
SELECT TOP 5 customers.customerid,orderid,orders.freight,customers.customerid,customers.contactname from customers, orders WHERE customers.customerid=orders.customerid ORDER BY 1
Returns 6 entries.
SELECT TOP 5 customers.customerid,orderid,orders.freight,customers.customerid,customers.contactname from customers, orders WHERE customers.customerid=orders.customerid ORDER BY 1,2
Returns 5 entries.
Just something to be aware of.
Comments