Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Select 3 records
Message
 
 
À
12/03/2008 18:51:20
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Divers
Thread ID:
01301530
Message ID:
01301597
Vues:
15
>>>Here is an interesting question. Is it possible to construct a sql-select statement that will return 3 adjacent records with the wanted keyfield in the middle? That is, a specified record WHERE keyfield = lnVal plus the record before and the record after. The 2 boundary records would be for the purpose of identifying the previous and next records for navigation purposes.
>>
>>You might be able to brute-force it by UNIONing 3 separate SELECTs, one each for the previous, current, and next rows. Rough pseudo-code:
>>
>>SELECT ;
>>  ... ;
>>  WHERE PKey = lnVal ;
>>  UNION ;
>>    SELECT TOP 1 ;
>>    ...
>>    WHERE PKey < lnVal ;
>>    ORDER BY ... DESC
>>  UNION ;
>>    SELECT TOP 1 ;
>>    ...
>>    WHERE PKey > lnVal ;
>>    ORDER BY ... ASC
>>
>>
>>
>>You'd need special handling when previous or next rows don't exist, or if another user INSERTs a new record that would be the "true" previous or next record while the first user is pondering her initial result set.
>
>Thanks Al. This might work if the base table sort was on the keyfield but if the sort was on something else like NAME we'd have a problem.

Do you mean you want physical previous and next records ? Do you have deleted records in the table?

Theoretically you can slightly modify Al's idea to use based on recno() select, but it would not work exactly if you have deleted records...

UPDATE, Tried to figure this recno() based select and failed so far.

I don't think we can use

select * from ... where recno() between (one select) and (another select).

Doesn't seem kosher to me.
If it's not broken, fix it until it is.


My Blog
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform