This has to be correct because there is no way of knowing what the top N would be without getting all matching records, ordering them, then returning the TOP N.

I think the idea is good if you can bracket the value in the where clause. For this particular question, the point is moot because his backend DB does not support TOP N clause.

I liked my other solution better that should work for all SQL DBs:

NEXT: select min(drawing_code) from mytable where drawing code > current_code
PREV: select max(drawing_code) from mytable where drawing code < current_code

Then use the return value to retrieve the data.



>Am I being dense or is this possibly quite time consuming? I've always thought that "Top N" code got ALL the records that meet the WHERE/JOIN conditions then ordered them and finally took the Top N records. This could be a problem if his present drawing was number 3 of 10,000,000 and he only wanted number 4.
>
>Barbara (hoping she's missed something)
>
>
>>Oops.. left out something...
>>
>>...WHERE drawingno < current_no ORDER BY drawingno DESCENDING
>>
>>or
>>
>>SELECT MAX(drawingno) FROM mytable WHERE drawingno < current_no
>>
>>I also wondered - if you are putting these in a VFP table first, why not order it by drawingno when you create the table? Then you can simply SKIP or SKIP -1...