PDA

Click to See Complete Forum and Search --> : Locate the last matching record

Queenie
10-29-2002, 10:45 PM
Hi,

I want to locate the last record which mtaches my searching requirement,
by using continue in a do-while loop is too slow for me, are there any faster
way?

Thanks very much!
Queenie
Carl Warner
10-29-2002, 11:36 PM
"Queenie" <qyu@hotmail.com> wrote:
>
>Hi,
>
>I want to locate the last record which mtaches my searching requirement,
>by using continue in a do-while loop is too slow for me, are there any faster
>way?
>

Is your searching requirement typically also an index key?

==Carl
Mike McCann
10-29-2002, 11:59 PM
Queenie wrote:
|| Hi,
||
|| I want to locate the last record which mtaches my searching
|| requirement, by using continue in a do-while loop is too slow for
|| me, are there any faster way?
||
|| Thanks very much!
|| Queenie

SELECT * FROM table WHERE ..... ORDER BY ....

GO BOTTOM
Vince Teachout
10-30-2002, 08:03 AM
On 29 Oct 2002 21:36:09 -0700, "Carl Warner" <Carl@VFUG.Org> wrote:

>>I want to locate the last record which mtaches my searching requirement,
>>by using continue in a do-while loop is too slow for me, are there any faster
>>way?
>>
>
>Is your searching requirement typically also an index key?
>
>==Carl

Carl foolishly asked a question hinting at a possible answer, but instead of
supplying the answer, he tried to be coy and drag out the suspense, thus
allowing me to swoop in with the answer and take all the credit! :-D

If you're searching on an index key, you can use the SET ORDER .... DESCENDING
clause, which will reverse the index, bringing the higher values to the top.
Like so:

In a zipcode file, tag "ZIP" is on zip code.

SET ORDER TO TAG ZIP
SEEK "7690" && Locates "76901"
SET ORER TO TAG ZIP DESCENDING
SEEK "7690" && Locates "76908"

Ignore munged e-mail address above.
If necessary to reply via e-mail, please reply to:
caracal A T caracal D O T net
Queenie
10-30-2002, 10:58 AM
Vince Teachout <SendJunkMailTo@hotmail.com> wrote:
>On 29 Oct 2002 21:36:09 -0700, "Carl Warner" <Carl@VFUG.Org> wrote:
>
>>>I want to locate the last record which mtaches my searching requirement,
>>>by using continue in a do-while loop is too slow for me, are there any
faster
>>>way?
>>>
>>
>>Is your searching requirement typically also an index key?
>>
>>==Carl
>
>Carl foolishly asked a question hinting at a possible answer, but instead
of
>supplying the answer, he tried to be coy and drag out the suspense, thus
>allowing me to swoop in with the answer and take all the credit! :-D
>
>If you're searching on an index key, you can use the SET ORDER .... DESCENDING
>clause, which will reverse the index, bringing the higher values to the
top.
>Like so:
>
>In a zipcode file, tag "ZIP" is on zip code.
>
>SET ORDER TO TAG ZIP
>SEEK "7690" && Locates "76901"
>SET ORER TO TAG ZIP DESCENDING
>SEEK "7690" && Locates "76908"
>
>Ignore munged e-mail address above.
>If necessary to reply via e-mail, please reply to:
>caracal A T caracal D O T net


Thanks! This is exactly what I needed :)
Nancy Folsom
10-31-2002, 03:08 PM
On Wed, 30 Oct 2002 08:03:34 -0500, Vince Teachout
<SendJunkMailTo@hotmail.com> wrote:

>If you're searching on an index key, you can use the SET ORDER .... DESCENDING
>clause, which will reverse the index, bringing the higher values to the top.
>Like so:

A note of extreme caution about this technique. There are a couple of
obscure, but deadly, bugs in SET ORDER DESCENDING. KB article Q165410
<http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q165410>
summarizes the official bug.

I found that there is a related bug whereby buffering is ignored when
SET ORDER DESCENDING/ASCENDING is issued. So, that if two users are
adding records to a table that is buffered, and one user saves their
record while the other user is in the midst of adding their, but they
haven't saved it), they will see the other users record.

I changed my logic to not use SET ORDER ASCENDING/DESCENDING. (Luckily
the record count would be small so I could just use a SCAN to find the
last record in an index order.)



Nancy Folsom--FoxPro MVP
Author of _Debugging_Visual_FoxPro_Applications_
http://www.hentzenwerke.com/catalogavailability/debugvfp.htm

In order to best serve the community, please post to the public
forum. I only reply to private requests for support from clients.
Nancy Folsom
10-31-2002, 03:09 PM
On Tue, 29 Oct 2002 22:59:33 -0600, "Mike McCann" <mcsoft@cox.net>
wrote:

>SELECT * FROM table WHERE ..... ORDER BY ....

Unfortunately this won't help if one is looking at a buffered table.


Nancy Folsom--FoxPro MVP
Author of _Debugging_Visual_FoxPro_Applications_
http://www.hentzenwerke.com/catalogavailability/debugvfp.htm

In order to best serve the community, please post to the public
forum. I only reply to private requests for support from clients.
Mike McCann
10-31-2002, 09:15 PM
Nancy Folsom wrote:
|| On Tue, 29 Oct 2002 22:59:33 -0600, "Mike McCann" <mcsoft@cox.net>
|| wrote:
||
||| SELECT * FROM table WHERE ..... ORDER BY ....
||
|| Unfortunately this won't help if one is looking at a buffered table.

Come again? Why ever, would you want to do that Nancy?

--
Mike
Nancy Folsom
11-01-2002, 01:02 PM
On Thu, 31 Oct 2002 20:15:59 -0600, "Mike McCann" <mcsoft@cox.net>
wrote:

>Nancy Folsom wrote:
>|| On Tue, 29 Oct 2002 22:59:33 -0600, "Mike McCann" <mcsoft@cox.net>
>|| wrote:
>||
>||| SELECT * FROM table WHERE ..... ORDER BY ....
>||
>|| Unfortunately this won't help if one is looking at a buffered table.
>
>Come again? Why ever, would you want to do that Nancy?

Let's say you're using table buffering to add one or more records to a
table (so that users can undo all their changes, if they want). If you
need to check whether something is in the buffered part of the
changes, this won't find those records.


Nancy Folsom--FoxPro MVP
Author of _Debugging_Visual_FoxPro_Applications_
http://www.hentzenwerke.com/catalogavailability/debugvfp.htm

In order to best serve the community, please post to the public
forum. I only reply to private requests for support from clients.
Mike McCann
11-01-2002, 04:32 PM
Nancy Folsom wrote:
|| On Thu, 31 Oct 2002 20:15:59 -0600, "Mike McCann" <mcsoft@cox.net>
|| wrote:
||
||| Nancy Folsom wrote:
||||| On Tue, 29 Oct 2002 22:59:33 -0600, "Mike McCann" <mcsoft@cox.net>
||||| wrote:
|||||
|||||| SELECT * FROM table WHERE ..... ORDER BY ....
|||||
||||| Unfortunately this won't help if one is looking at a buffered
||||| table.
|||
||| Come again? Why ever, would you want to do that Nancy?
||
|| Let's say you're using table buffering to add one or more records to
|| a table (so that users can undo all their changes, if they want). If
|| you need to check whether something is in the buffered part of the
|| changes, this won't find those records.
||

But those records wouldn't be indexed either (would they?) so I would
think the SQL against the table would be faster, JMHO.

--
Mike
Stefan Wuebbe
11-02-2002, 07:27 AM
"Mike McCann" <mcsoft@cox.net> schrieb im Newsbeitrag
news:3dc2f0a0$1@tnews.web.devx.com...
> Nancy Folsom wrote:
> || On Thu, 31 Oct 2002 20:15:59 -0600, "Mike McCann" <mcsoft@cox.net>
> || wrote:
> ||
> ||| Nancy Folsom wrote:
> ||||| On Tue, 29 Oct 2002 22:59:33 -0600, "Mike McCann" <mcsoft@cox.net>
> ||||| wrote:
> |||||
> |||||| SELECT * FROM table WHERE ..... ORDER BY ....
> |||||
> ||||| Unfortunately this won't help if one is looking at a buffered
> ||||| table.
> |||
> ||| Come again? Why ever, would you want to do that Nancy?
> ||
> || Let's say you're using table buffering to add one or more records to
> || a table (so that users can undo all their changes, if they want). If
> || you need to check whether something is in the buffered part of the
> || changes, this won't find those records.
> ||
>
> But those records wouldn't be indexed either (would they?)

Yes, they would.

> so I would
> think the SQL against the table would be faster, JMHO.

I believe there would be no difference. But anyway, in Nancy's
example you would not want to do so:
CREATE TABLE test22 (test I)
INDEX ON test TAG test
INSERT INTO test22 VALUES (1)
* switch buffering on
SET MULTILOCKS ON
CURSORSETPROP("Buffering",5)
* add a buffered rec, not written to FS
INSERT INTO test22 VALUES (2)
SELECT * from test22 && gives 1 only
? SEEK(2,'test22','Test') && .T., buffered rec is indexed
* cleanup
&& USE, USE IN test22, ERASE test22.*


-Stefan
Mike McCann
11-02-2002, 10:10 AM
Stefan Wuebbe wrote:
|| "Mike McCann" <mcsoft@cox.net> schrieb im Newsbeitrag
|| news:3dc2f0a0$1@tnews.web.devx.com...
||| Nancy Folsom wrote:
||||| On Thu, 31 Oct 2002 20:15:59 -0600, "Mike McCann" <mcsoft@cox.net>
||||| wrote:
|||||
|||||| Nancy Folsom wrote:
|||||||| On Tue, 29 Oct 2002 22:59:33 -0600, "Mike McCann"
|||||||| <mcsoft@cox.net> wrote:
||||||||
||||||||| SELECT * FROM table WHERE ..... ORDER BY ....
||||||||
|||||||| Unfortunately this won't help if one is looking at a buffered
|||||||| table.
||||||
|||||| Come again? Why ever, would you want to do that Nancy?
|||||
||||| Let's say you're using table buffering to add one or more records
||||| to a table (so that users can undo all their changes, if they
||||| want). If you need to check whether something is in the buffered
||||| part of the changes, this won't find those records.
|||||
|||
||| But those records wouldn't be indexed either (would they?)
||
|| Yes, they would.
||
||| so I would
||| think the SQL against the table would be faster, JMHO.
||
|| I believe there would be no difference. But anyway, in Nancy's
|| example you would not want to do so:
|| CREATE TABLE test22 (test I)
|| INDEX ON test TAG test
|| INSERT INTO test22 VALUES (1)
|| * switch buffering on
|| SET MULTILOCKS ON
|| CURSORSETPROP("Buffering",5)
|| * add a buffered rec, not written to FS
|| INSERT INTO test22 VALUES (2)
|| SELECT * from test22 && gives 1 only
|| ? SEEK(2,'test22','Test') && .T., buffered rec is indexed
|| * cleanup
|| && USE, USE IN test22, ERASE test22.*
||

Sorry, Stefan, I wasn't asking Nancy the mechanics of a trivial example.
Nancy Folsom
11-02-2002, 11:40 AM
Mike-

>Sorry, Stefan, I wasn't asking Nancy the mechanics of a trivial example.

Not sure why you're slamming Stefan for taking the trouble to reply.
His example was clear in illustrating the point. I, too, had assumed
it wasn't obvious to you, and while it might have been, there may be
other readers who are interested in the example.


Nancy Folsom--FoxPro MVP
Author of _Debugging_Visual_FoxPro_Applications_
http://www.hentzenwerke.com/catalogavailability/debugvfp.htm

In order to best serve the community, please post to the public
forum. I only reply to private requests for support from clients.
Nancy Folsom
11-02-2002, 11:41 AM
On Fri, 1 Nov 2002 15:32:40 -0600, "Mike McCann" <mcsoft@cox.net>
wrote:

>
>But those records wouldn't be indexed either (would they?) so I would
>think the SQL against the table would be faster, JMHO.

Of course buffered records are indexed. It's not a speed question
anyway. It is the simple fact that if you need to included buffered
records in your analysis, you cannot use a SQL-Select since it
executes against the file on disk.


Nancy Folsom--FoxPro MVP
Author of _Debugging_Visual_FoxPro_Applications_
http://www.hentzenwerke.com/catalogavailability/debugvfp.htm

In order to best serve the community, please post to the public
forum. I only reply to private requests for support from clients.
Nancy Folsom
11-02-2002, 11:42 AM
Stefan-


>> so I would
>> think the SQL against the table would be faster, JMHO.
>
>I believe there would be no difference. But anyway, in Nancy's
>example you would not want to do so:
> CREATE TABLE test22 (test I)
> INDEX ON test TAG test
> INSERT INTO test22 VALUES (1)
>* switch buffering on
> SET MULTILOCKS ON
> CURSORSETPROP("Buffering",5)
>* add a buffered rec, not written to FS
> INSERT INTO test22 VALUES (2)
> SELECT * from test22 && gives 1 only
> ? SEEK(2,'test22','Test') && .T., buffered rec is indexed
>* cleanup
> && USE, USE IN test22, ERASE test22.*

Thanks for the example. You're correctly interpreting my comment, and
the example is simpler than the one I would have come up with. :)
>
>-Stefan



Nancy Folsom--FoxPro MVP
Author of _Debugging_Visual_FoxPro_Applications_
http://www.hentzenwerke.com/catalogavailability/debugvfp.htm

In order to best serve the community, please post to the public
forum. I only reply to private requests for support from clients.
Mike McCann
11-02-2002, 01:28 PM
Nancy Folsom wrote:
|| Mike-
||
||| Sorry, Stefan, I wasn't asking Nancy the mechanics of a trivial
||| example.
||
|| Not sure why you're slamming Stefan for taking the trouble to reply.
|| His example was clear in illustrating the point. I, too, had assumed
|| it wasn't obvious to you, and while it might have been, there may be
|| other readers who are interested in the example.
||
Not an intended slam, however, if you would like to continue......
Nancy Folsom
11-02-2002, 02:26 PM
On Sat, 2 Nov 2002 12:28:04 -0600, "Mike McCann" <mcsoft@cox.net>
wrote:

>Not an intended slam, however, if you would like to continue......

Sorry, Mike, but you've totally lost me on what your issue is, so I'll
just take a bye.


Nancy Folsom--FoxPro MVP
Author of _Debugging_Visual_FoxPro_Applications_
http://www.hentzenwerke.com/catalogavailability/debugvfp.htm

In order to best serve the community, please post to the public
forum. I only reply to private requests for support from clients.
Mike McCann
11-02-2002, 06:12 PM
Nancy Folsom wrote:
|| On Sat, 2 Nov 2002 12:28:04 -0600, "Mike McCann" <mcsoft@cox.net>
|| wrote:
||
||| Not an intended slam, however, if you would like to continue......
||
|| Sorry, Mike, but you've totally lost me on what your issue is, so
|| I'll just take a bye.

Oh, but it was you that came up with a trivial issue, not me. I still
cannot see any value you have added to this discussion, except reminding
us of how buffering works. Thanks anyway.
Stefan Wuebbe
11-03-2002, 05:08 AM
Hi Nancy

Nancy Folsom schrieb im Newsbeitrag
news:l108suchaif9f2ur1thhmd1bj33bv4u735@4ax.com...
> Stefan-
>
<snip>
> Thanks for the example. You're correctly interpreting my comment, and
> the example is simpler than the one I would have come up with. :)

Somehow I doubt the latter .. <s>

Regards
-Stefan
Nancy Folsom
11-03-2002, 11:03 AM
On Sat, 2 Nov 2002 17:12:39 -0600, "Mike McCann" <mcsoft@cox.net>
wrote:

>Oh, but it was you that came up with a trivial issue, not me. I still
>cannot see any value you have added to this discussion, except reminding
>us of how buffering works. Thanks anyway.

I disagree that a reminder that SQL Selects select from the source on
disk, and does not account for buffered records is trivial. I've had
two apps in recent time that needed search in buffered data, and the
question comes up periodically.

Not all of us are as advanced as you and thus not everyone may think
it's as trivial a point. But thanks for sharing your opinion of those
of us who may be interested in this.


Nancy Folsom--FoxPro MVP
Author of _Debugging_Visual_FoxPro_Applications_
http://www.hentzenwerke.com/catalogavailability/debugvfp.htm

In order to best serve the community, please post to the public
forum. I only reply to private requests for support from clients.
Joe \Nuke Me Xemu\ Foster
11-03-2002, 07:48 PM
"Mike McCann" <mcsoft@cox.net> wrote in message <news:3dc4598e$1@tnews.web.devx.com>...

> Nancy Folsom wrote:
> || On Sat, 2 Nov 2002 12:28:04 -0600, "Mike McCann" <mcsoft@cox.net>
> || wrote:
> ||
> ||| Not an intended slam, however, if you would like to continue......
> ||
> || Sorry, Mike, but you've totally lost me on what your issue is, so
> || I'll just take a bye.
>
> Oh, but it was you that came up with a trivial issue, not me. I still
> cannot see any value you have added to this discussion, except reminding
> us of how buffering works. Thanks anyway.

Come again? Why ever, would you want to feebly attempt to
cover up a little understandable ignorance like that Mikey?

--
Joe Foster <mailto:jlfoster%40znet.com> L. Ron Dullard <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
Mike McCann
11-03-2002, 08:44 PM
Joe wrote:
|| "Mike McCann" <mcsoft@cox.net> wrote in message
|| <news:3dc4598e$1@tnews.web.devx.com>...
||
||| Nancy Folsom wrote:
||||| On Sat, 2 Nov 2002 12:28:04 -0600, "Mike McCann" <mcsoft@cox.net>
||||| wrote:
|||||
|||||| Not an intended slam, however, if you would like to
|||||| continue......
|||||
||||| Sorry, Mike, but you've totally lost me on what your issue is, so
||||| I'll just take a bye.
|||
||| Oh, but it was you that came up with a trivial issue, not me. I
||| still cannot see any value you have added to this discussion,
||| except reminding us of how buffering works. Thanks anyway.
||
|| Come again? Why ever, would you want to feebly attempt to
|| cover up a little understandable ignorance like that Mikey?
||
Joey, didn't realize you were an officianado of VFP, thought you were an
pseudonym of Zane Thomas.
Zane Thomas
11-03-2002, 11:37 PM
On Sun, 3 Nov 2002 21:16:00 -0800, "Joe \"Nuke Me Xemu\" Foster"
<joe@bftsi0.UUCP> wrote:

>Cite?

Did I say that?


--

*--------={ Fine Art for .NET }=--------*
| .Net Components @ www.abderaware.com |
*---------------------------------------*

Turn on, tune in, download.
zane a@t abderaware.com
Joe \Nuke Me Xemu\ Foster
11-04-2002, 12:16 AM
"Mike McCann" <mcsoft@cox.net> wrote in message <news:3dc5ce92$1@tnews.web.devx.com>...

> Joe wrote:

> || "Mike McCann" <mcsoft@cox.net> wrote in message

> ||| Oh, but it was you that came up with a trivial issue, not me. I
> ||| still cannot see any value you have added to this discussion,
> ||| except reminding us of how buffering works. Thanks anyway.
> ||
> || Come again? Why ever, would you want to feebly attempt to
> || cover up a little understandable ignorance like that Mikey?
> ||

> Joey, didn't realize you were an officianado of VFP, thought you were an
> pseudonym of Zane Thomas.

Cite?

Anyway, there are some very good reasons for querying against a
buffered table, assuming "buffered tables" roughly correspond to
an open transaction in DAO/ADO/TLADuJour.NET. A SELECT SUM could
easily recalculate a total, or finding the "last matching record"
could be handy when implementing a multi-level undo. Duh.

--
Joe Foster <mailto:jlfoster%40znet.com> Sacrament R2-45 <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
Nancy Folsom
11-04-2002, 01:31 AM
On Sun, 3 Nov 2002 21:16:00 -0800, "Joe \"Nuke Me Xemu\" Foster"
<joe@bftsi0.UUCP> wrote:

>Anyway, there are some very good reasons for querying against a
>buffered table, assuming "buffered tables" roughly correspond to
>an open transaction in DAO/ADO/TLADuJour.NET. A SELECT SUM could
>easily recalculate a total, or finding the "last matching record"
>could be handy when implementing a multi-level undo. Duh.

Good example. In another example, users were allowed to add a fix
number of items (one in this case) from a selection. They could, and
did, add them in any order. I had to account for the buffered records,
of course.


Nancy Folsom--FoxPro MVP
Author of _Debugging_Visual_FoxPro_Applications_
http://www.hentzenwerke.com/catalogavailability/debugvfp.htm

In order to best serve the community, please post to the public
forum. I only reply to private requests for support from clients.
Fred Taylor
11-04-2002, 10:11 AM
Ahh, but see, that's the beauty of FoxPro. It has commands other than the
SELECT functions to deal with buffered records. You have the COUNT, SUM,
etc. commands to deal with buffered data. (I know you know that Nancy ;) )
--

Fred
Microsoft Visual FoxPro MVP
Please respond only to the newsgroups so that all may benefit.





Nancy Folsom wrote:
> On Sun, 3 Nov 2002 21:16:00 -0800, "Joe \"Nuke Me Xemu\" Foster"
> <joe@bftsi0.UUCP> wrote:
>
>> Anyway, there are some very good reasons for querying against a
>> buffered table, assuming "buffered tables" roughly correspond to
>> an open transaction in DAO/ADO/TLADuJour.NET. A SELECT SUM could
>> easily recalculate a total, or finding the "last matching record"
>> could be handy when implementing a multi-level undo. Duh.
>
> Good example. In another example, users were allowed to add a fix
> number of items (one in this case) from a selection. They could, and
> did, add them in any order. I had to account for the buffered records,
> of course.
>
>
> Nancy Folsom--FoxPro MVP
> Author of _Debugging_Visual_FoxPro_Applications_
> http://www.hentzenwerke.com/catalogavailability/debugvfp.htm
>
> In order to best serve the community, please post to the public
> forum. I only reply to private requests for support from clients.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.410 / Virus Database: 231 - Release Date: 10/31/2002
Nancy Folsom
11-04-2002, 11:11 AM
On Mon, 4 Nov 2002 08:11:04 -0700, "Fred Taylor" <ftaylor@mvps.org>
wrote:

>Ahh, but see, that's the beauty of FoxPro. It has commands other than the
>SELECT functions to deal with buffered records. You have the COUNT, SUM,
>etc. commands to deal with buffered data. (I know you know that Nancy ;) )

Which was rather my original point.


Nancy Folsom--FoxPro MVP
Author of _Debugging_Visual_FoxPro_Applications_
http://www.hentzenwerke.com/catalogavailability/debugvfp.htm

In order to best serve the community, please post to the public
forum. I only reply to private requests for support from clients.
Mike McCann
11-04-2002, 07:37 PM
Nancy Folsom wrote:
|| On Sun, 3 Nov 2002 21:16:00 -0800, "Joe \"Nuke Me Xemu\" Foster"
|| <joe@bftsi0.UUCP> wrote:
||
||| Anyway, there are some very good reasons for querying against a
||| buffered table, assuming "buffered tables" roughly correspond to
||| an open transaction in DAO/ADO/TLADuJour.NET. A SELECT SUM could
||| easily recalculate a total, or finding the "last matching record"
||| could be handy when implementing a multi-level undo. Duh.
||
|| Good example. In another example, users were allowed to add a fix
|| number of items (one in this case) from a selection. They could, and
|| did, add them in any order. I had to account for the buffered
|| records, of course.
||
Next, I suppose you and Zane er, Joey or whoever posted that drivel
above yours will be tolling the virtues of table locking strategies.
Table locking will assure that you will get to scan every record in its
pristine form before commiting the thousands of otherwise buffered
records you would have to maintain in your Cooperesque world of infinite
rollback capability. Ever work in a multi user system? How do you see
into all those other evil buffers out there with your magical SCAN
technology, anyway? I would have expected better from "experts".