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.