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
>Try
SET @LastFirst = @LastName + @FirstName -- from the previous record.
>SELECT TOP 1 *
>FROM yourTable
>WHERE LastName >= @lastName
> AND FirstName >= @firstName
> AND LastName + FirstName > @LastFirst
> ORDER BY lastname, firstname
>-- or
>SELECT TOP 1 *
>FROM yourTable
>WHERE LastName >= @lastName
> AND FirstName >= @firstName
> AND custID <> @custID
> ORDER BY lastname, firstname
>
>
>>Dear Michael,
>>
>>Thank you for your response. However I am afraid the command may not be able to return the correct record since the following customer will not satisfy the WHERE condition of the SQL even if it is actually the alphabetical next.
>>CustID=73, LastName='Barret', FirstName='Amy'
>>
>>KM
>>
>>