SQL TOP N - Watch Out in VFP 8 and earlier

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
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
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

Popular Posts