Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
SQL to get the alphabetical next
Message
From
01/04/2003 00:52:24
Km Kwun
Eastop Consultants Limited
Nt, Hong Kong
 
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Miscellaneous
Thread ID:
00771820
Message ID:
00772268
Views:
24
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
>>
>>
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform