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 <[email protected]> >To: ProFox Email List <[email protected]> >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: [email protected] >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: [email protected] 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/[email protected] ** 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.
