>I'd like to use SQL Syntax to make the code more compatible when using SQL Server for the back end. Can anyone see a flaw in these statements?
>
>The problem is to find the primary key of the record with the next highest primary key after #1700001.
>
>In old-style fox syntax, that's:
> set order to pri_key
> seek 1700001
> skip
> ?pri_key
>
>The table has 112,000 records, over 100 columns and some large memos. The primary key is a double (8 bytes). SYS(3054,11) tells me the query is fully optimized.
>
>
>
>This one takes just over 30 seconds (fresh start of VFP - no cached data)
>select min(guest_no);
> from mytable;
> where pri_key>1700001 nofilter
>
>
>This one takes just over 30 seconds (fresh start of VFP - no cached data)
>select top 1 guest_no;
> from mytable;
> where pri_key>1700001;
> order by guest_no nofilter
>
>
>This one takes just over 1 second (fresh start of VFP - no cached data)
>select guest_no;
> from mytable;
> where pri_key>1700001;
> order by guest_no
>locate
>
>
>I am awestruck, but the clear winner, at far less than 1 second is:
> set order to pri_key
> seek 1700001
> skip
>
>Thanks,
>Mike

I'm probably a victim of foot-in-mouth disease again but the last block of code doesn't do the same thing as the SQL statements. It simply finds the next record after pri_key 1700001 and doesn't do anything with guest_no

Is the objective to find the guest number for that record? If so, I'd try

SELECT guest_no FROM myTable WHERE pri_key IN (SELECT MIN(pri_key) FROM MyTable WHERE pri_key > 1700001)

Or is the objective to find the smallest guest_no out of all records past pri_key 1700001 (which is the way I'm reading your SQL statements)???

I'm curious about what I've missed here