Hi Km,
I think following query should work for you. You've to use
TOP 1 to get one record and
ORDER BY to get NEXT alphabetical record.
SELECT TOP 1 *
FROM MyTable
WHERE LastName > @lastName
OR (LastName=@LastName AND FirstName > @FirstName )
OR (LastName=@LastName AND FirstName = @FirstName AND CustID > @CustID)
ORDER BY lastname, firstname
The SQL Server query optimizer is much more sophisticated than in VFP but it's hard to tell how your query will perform w/o actually run it.
>Dear Sergey and Michael,
>
>Thanks for your suggestions. However I'm afraid the code is still not able to return the correct record. Take the following data as an example:
>CustID=73, LastName='Barret', FirstName='Amy'
>"Berret, Amy" is alphabetically greater than "Barnes, John", but "Amy" is smaller than "John"
>
>I'm thinking about the following command but I don't know if this is a correct and good statement. Grateful if you could advise.
>SELECT *
>FROM MyTable
>WHERE (LastName > @lastName
> OR (LastName=@LastName AND FirstName>=@FirstName))
> AND CustID>@CustID
>
>By the way, I found that the TOP keyword is sometimes slow in VFP if the record set defined by the WHERE clause is large, since VFP have to firstly retrieve the large records set and then sort them and return the top one. I'm a newbie in SQL server, I don't know if the mechanism in SQL server is similar.
>
>KM
>
>
--sb--