Index
2012-07-10 07:09 – Gérard Lochon : Months later ... i have an SQL soluce for "what is the rank of this data in an index order?"
2012-07-10 08:08 – Eurico Chagas Filho : Re: Months later ... i have an SQL soluce for "what is the rank of this data in an index order?"
2012-07-10 08:15 – Stephen Russell : Re: Months later ... i have an SQL soluce for "what is the rank of this data in an index order?"
2012-07-10 08:25 – Jean MAURICE : Re: Months later ... i have an SQL soluce for "what is the rank of this data in an index order?"
Back to top
Months later ... i have an SQL soluce for "what is the rank of this data in an index order?"

Author: Gérard Lochon

Posted: 2012-07-10 07:09:00   Link

Hello guys.

I was reading a lot of 'old threads', before deleting them from my computer.

And i came across this one, which answers did not satisfy me :

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

Michael Savage : (tuesday, 20 septembre 2011 19:03)

I have a table, with two indexes. (In a cdx file)

I need to know the new place in the table when the order changes.

ie if it is the second record in the table in sort order #1, what is it

in order #2.

Let say that in order 1 it is the 2nd record, but in order #2 it would

be the fifth record.

How do I find out when I switch order, what position it's in?

Can't use recno(), what other functions is there.

Mike

------------------

Fred Taylor :

There are no functions that return a position relative to the index. If

there aren't too many records, you may be able to SELECT RECNO() FROM table

ORDER BY your_order INTO ARRAY xxxx, then search the array for the RECNO()

you want. The found index into the array will be your relative position.

This is limited to about 65000 records for VFP8 or earlier. VFP9 is only

limited by available RAM.

In further thinking, you could do the SELECT ... INTO CURSOR and find your

RECNO() field value. The RECNO() of the cursor would be the relative

position. Should work in all versions of VFP.

-----------------

Tracy Pearson :

Michael,

The only sure way I came up with is using some select statements

LOCAL currentrow, record_position_01, record_position_02, keyvalue

SET ORDER TO 2

m.currentrow = EVALUATE(KEY(1))

m.keyvalue = KEY(1)

SELECT (KEY(1)) FROM (ALIAS()) TO SCREEN NOCONSOLE WHERE

&keyvalue.<=m.currentrow

m.record_position_01 = _TALLY

SET ORDER TO 2

m.currentrow = EVALUATE(KEY(2))

m.keyvalue = KEY(2)

SELECT (KEY(2)) FROM (ALIAS()) TO SCREEN NOCONSOLE WHERE

&keyvalue.<=m.currentrow

m.record_position_02 = _TALLY

?m.record_position_01,m.record_position_02

I'm not sure why position is needed, unless you are dealing with a TreeView

type of positioning.

-----------------

Alan Bourke :

Select mytable

set order to tag order1

goto top

lvKeyvalue = mytable.field1

locate for field1 != lvKeyvalue

lnResult1 = Recno()

set order to tag order2

goto top

lvKeyvalue = mytable.field1

locate for field1 != lvKeyvalue

lnResult2 = Recno()

-------------------------------------------------------------

-------------------------------------------------------------

Well, you know,

there IS a solution using VFP - SQL and valid RECNO() abilities

(when recno() can be formally anchored within a data subset) :

Using the following example :

[VFP]

CREATE CURSOR test ;

(lib c(10),mt i)

INSERT INTO test VALUES ("john",10)

INSERT INTO test VALUES ("jennifer",15)

INSERT INTO test VALUES ("stella",25)

INSERT INTO test VALUES ("sophia",40)

INSERT INTO test VALUES ("greg",50)

INSERT INTO test VALUES ("jeff",20)

[/VFP]

(in this example the tow indexes criterias are set on 'lib' and 'mt')

We can create a dataset collating all the information needed,

like this :

[VFP]

SELECT xx.lib, xx.rank1,yy.rank2 FROM ;

(SELECT lib,mt, RECNO() as rank2 FROM ;

(SELECT lib,mt, RECNO() as rank FROM test ;

ORDER BY 2) as yy) as yy, ;

(SELECT lib,mt, RECNO() as rank1 FROM ;

(SELECT lib,mt, RECNO() as rank FROM test ;

ORDER BY 1) as xx) as xx ;

WHERE xx.lib = yy.lib

[/VFP]

Then, it is possible to restrict the result set using additional

WHERE clauses like AND lib = "john", or rank1 = 3.

Furthermore, using macrosubstitution, it is possible

to completely parametrize the request using EVAL(KEY(n)) ...

SQL is great.

Have good fun !

Gérard.

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/EED16589D39B498BA4E5009F9FECE1A2@MuriellePC

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

©2012 Gérard Lochon
searchNew Search
Back to top
Re: Months later ... i have an SQL soluce for "what is the rank of this data in an index order?"

Author: Eurico Chagas Filho

Posted: 2012-07-10 08:08:06   Link

Well, in any order search for the smallest index, using softseek. That's the record where the

data for that index begins...

HTH

>________________________________

> From: Gérard Lochon <g-lochon@wanadoo.fr>

>To: ProFox Email List <profox@leafe.com>

>Sent: Tuesday, July 10, 2012 9:09 AM

>Subject: Months later ... i have an SQL soluce for "what is the rank of this data in an index order?"

>

>Hello guys.

>

>I was reading a lot of 'old threads', before deleting them from my computer.

>

>And i came across this one, which answers did not satisfy me :

>

>--------------------------------------------------------------------------------------

>--------------------------------------------------------------------------------------

>

>Michael Savage :  (tuesday, 20 septembre 2011 19:03)

>

>I have a table, with two indexes. (In a cdx file)

>

>I need to know the new place in the table when the order changes.

>ie if it is the second record in the table in sort order #1, what is it

>in order #2.

>

>Let say that in order 1 it is the 2nd record, but in order #2 it would

>be the fifth record.

>How do I find out when I switch order, what position it's in?

>

>Can't use recno(), what other functions is there.

>Mike

>

>------------------

>

>Fred Taylor :

>

>There are no functions that return a position relative to the index.  If

>there aren't too many records, you may be able to SELECT RECNO() FROM table

>ORDER BY your_order INTO ARRAY xxxx, then search the array for the RECNO()

>you want.  The found index into the array will be your relative position.

>

>This is limited to about 65000 records for VFP8 or earlier.  VFP9 is only

>limited by available RAM.

>

>In further thinking, you could do the SELECT ... INTO CURSOR and find your

>RECNO() field value.  The RECNO() of the cursor would be the relative

>position.  Should work in all versions of VFP.

>

>-----------------

>

>Tracy Pearson :

>

>Michael,

>

>The only sure way I came up with is using some select statements

>

>      LOCAL currentrow, record_position_01, record_position_02, keyvalue

>      SET ORDER TO 2

>      m.currentrow = EVALUATE(KEY(1))

>      m.keyvalue = KEY(1)

>      SELECT (KEY(1)) FROM (ALIAS()) TO SCREEN NOCONSOLE WHERE

>&keyvalue.<=m.currentrow

>      m.record_position_01 = _TALLY

>      SET ORDER TO 2

>      m.currentrow = EVALUATE(KEY(2))

>      m.keyvalue = KEY(2)

>      SELECT (KEY(2)) FROM (ALIAS()) TO SCREEN NOCONSOLE WHERE

>&keyvalue.<=m.currentrow

>      m.record_position_02 = _TALLY

>

>      ?m.record_position_01,m.record_position_02

>

>I'm not sure why position is needed, unless you are dealing with a TreeView

>type of positioning.

>

>-----------------

>

>Alan Bourke :

>

>

>Select mytable

>set order to tag order1

>goto top

>lvKeyvalue = mytable.field1

>locate for field1 != lvKeyvalue

>lnResult1 = Recno()

>set order to tag order2

>goto top

>lvKeyvalue = mytable.field1

>locate for field1 != lvKeyvalue

>lnResult2 = Recno()

>

>-------------------------------------------------------------

>-------------------------------------------------------------

>

>Well, you know,

>

>there IS a solution using VFP - SQL and valid RECNO() abilities

>(when recno() can be formally anchored within a data subset) :

>

>

>Using the following example :

>

>[VFP]

>

>CREATE CURSOR test ;

>(lib c(10),mt i)

>

>INSERT INTO test VALUES ("john",10)

>INSERT INTO test VALUES ("jennifer",15)

>INSERT INTO test VALUES ("stella",25)

>INSERT INTO test VALUES ("sophia",40)

>INSERT INTO test VALUES ("greg",50)

>INSERT INTO test VALUES ("jeff",20)

>

>[/VFP]

>

>(in this example the tow indexes criterias are set on 'lib' and 'mt')

>

>We can create a dataset collating all the information needed,

>like this :

>

>[VFP]

>

>SELECT xx.lib, xx.rank1,yy.rank2 FROM ;

>(SELECT lib,mt, RECNO() as rank2 FROM ;

>(SELECT lib,mt, RECNO() as rank FROM test ;

>ORDER BY 2) as yy) as yy, ;

>(SELECT lib,mt, RECNO() as rank1 FROM ;

>(SELECT lib,mt, RECNO() as rank FROM test ;

>ORDER BY 1) as xx) as xx ;

>WHERE xx.lib = yy.lib

>

>[/VFP]

>

>Then, it is possible to restrict the result set using additional

>WHERE clauses like AND lib = "john", or rank1 = 3.

>

>Furthermore, using macrosubstitution, it is possible

>to completely parametrize the request using EVAL(KEY(n)) ...

>

>SQL is great.

>Have good fun !

>

>Gérard.

>

>

>

>_______________________________________________

>Post Messages to: ProFox@leafe.com

>Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

>OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

>Searchable Archive: http://leafe.com/archives/search/profox

>This message: http://leafe.com/archives/byMID/profox/EED16589D39B498BA4E5009F9FECE1A2@MuriellePC

>** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

>

>

>

--- StripMime Report -- processed MIME parts ---

multipart/alternative

text/plain (text body -- kept)

text/html

---

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/1341925686.9806.YahooMailNeo@web160805.mail.bf1.yahoo.com

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

©2012 Eurico Chagas Filho
searchNew Search
Back to top
Re: Months later ... i have an SQL soluce for "what is the rank of this data in an index order?"

Author: Stephen Russell

Posted: 2012-07-10 08:15:42   Link

On Tue, Jul 10, 2012 at 8:08 AM, Eurico Chagas Filho

<e28chagas@yahoo.com.br> wrote:

> Well, in any order search for the smallest index, using softseek. That's the record where the

> data for that index begins...

-----------------------

Is there any value to know the location # from BOF(), and if there is

why is closer better depending on index?

--

Stephen Russell

Sr. Analyst

Ring Container Technology

Oakland TN

901.246-0159 cell

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/CAJidMYJqw_8gfDWTZ9fK7uSQOjZKVsZceRF2rVczhUiEh1Ldhg@mail.gmail.com

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

©2012 Stephen Russell
searchNew Search
Back to top
Re: Months later ... i have an SQL soluce for "what is the rank of this data in an index order?"

Author: Jean MAURICE

Posted: 2012-07-10 08:25:55   Link

oh my god !!

Je n'aurais JAMAIS imaginé imbriquer des SELECT dans la clause FROM !!!!

I would never dare use a SELECT in a FROM clause of a SELECT statement being

itself in a FROM clause of a 'upper' SELECT !!!

The Foxil

Jean à Grenoble

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/4FFC2D63.5070405@wanadoo.fr

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

©2012 Jean MAURICE
searchNew Search