I think you should change ORDER BY clause to
ORDER BY lastname, firstname, CustID
otherwise you could miss some records under following conditions
- First and last name are the same
- There are more than 2 such records
- CustID values arent in sequential order in those records.
>Hi Sergey,
>
>Yes, the statement is exactly what I want. Thanks.
>
>KM
>
>
>>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--