Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread Autoinc difference between V3 and V4
Sun, Dec 17 2006 7:31 PMPermanent Link

"Robert"
In V3, adding an autoinc field would give you a sequence number starting
from 1 thru number of records. Example

SELECT f1, f2 into memory temp from table order by f1;
alter table memory temp add f3 autoinc;

would generate a sequence in the order of the table, starting at 1 for the
first row. That is not tha case anymore in V4, because DBISAM uses an
internal index, not record position.

How can one accomplish the same thing in V4? That is, getting a "record
number" such as

select a, b from table order by b, a

and end up with

a  b   record number
1  1  1
5  1  2
7  2  3
1  3  4
etc

Robert

Mon, Dec 18 2006 1:58 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Robert,

<< In V3, adding an autoinc field would give you a sequence number starting
from 1 thru number of records. Example

SELECT f1, f2 into memory temp from table order by f1;
alter table memory temp add f3 autoinc;

would generate a sequence in the order of the table, starting at 1 for the
first row. That is not tha case anymore in V4, because DBISAM uses an
internal index, not record position.

How can one accomplish the same thing in V4? That is, getting a "record
number" such as

select a, b from table order by b, a >>

Some folks use RUNSUM(1) to do it, but that would require a GROUP BY clause,
which I'm not sure is possible in your case.  Other than that, you'll most
likely have to use code.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Dec 19 2006 11:26 AMPermanent Link

"Donat Hebert \(Worldsoft\)"
If you optimize the table as an intermediate step, that will put it in the
physical order you requested in the Order by.
Give this a whirl ... (my sample test)

SELECT CostCentre, Description
into atemp
from BgCostc
order by 1;

Optimize Table atemp;

alter Table ATemp
 add RecIdent AutoInc at 1 ;

I believe we got this tip from Tim to place the table in correct sequence
before we deleted duplicates (ie complex sorts)
Let me know if you find something different.

Donat.


"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:9A7330A8-87D5-4F97-AEF1-F8D4E2665065@news.elevatesoft.com...
> In V3, adding an autoinc field would give you a sequence number starting
> from 1 thru number of records. Example
>
> SELECT f1, f2 into memory temp from table order by f1;
> alter table memory temp add f3 autoinc;
>
> would generate a sequence in the order of the table, starting at 1 for the
> first row. That is not tha case anymore in V4, because DBISAM uses an
> internal index, not record position.
>
> How can one accomplish the same thing in V4? That is, getting a "record
> number" such as
>
> select a, b from table order by b, a
>
> and end up with
>
> a  b   record number
> 1  1  1
> 5  1  2
> 7  2  3
> 1  3  4
> etc
>
> Robert
>

Tue, Dec 19 2006 12:49 PMPermanent Link

"Robert"

"Donat Hebert (Worldsoft)" <donat.hebert@worldsoft.ca> wrote in message
news:E8DC0AD2-668D-41C8-B831-CA85F717F8DF@news.elevatesoft.com...
> If you optimize the table as an intermediate step, that will put it in the
> physical order you requested in the Order by.
> Give this a whirl ... (my sample test)
>

Clever, thanks. I'll try it out, but is should work. An you only have to add
a line to the SQL script, not bad.

Robert

Image