I want to have a runcount that resets for every group, so for example:

Group A,  Rec 1   1
Group A, Rec 2    2
Group A, Rec 3,   3
Group B,  Rec 1   1
Group B, Rec 2    2
Group C,  Rec 1   1
Group C, Rec 2    2
Group C, Rec 3,   3

etc

How is this achievable in an SQL?   V3.30

Thanks

Alex

"Al Vas" <forgetit@forgetit.com> wrote in news:EC3EC3EE-4D36-48DE-A59F-
D99D4F659B23@news.elevatesoft.com:

> I want to have a runcount that resets for every group, so for example:
>
> Group A,  Rec 1   1
> Group A, Rec 2    2
> Group A, Rec 3,   3
> Group B,  Rec 1   1
> Group B, Rec 2    2
> Group C,  Rec 1   1
> Group C, Rec 2    2
> Group C, Rec 3,   3
>
> etc
>
> How is this achievable in an SQL?   V3.30
>

I don't know about v3, but in v4 this works:

-------------------------8<------------------------------
DROP TABLE IF EXISTS "\memory\tmp1"
;
DROP TABLE IF EXISTS "\memory\tmp2"
;

CREATE TABLE "\memory\tmp1" (
 ID AUTOINC,
 PostCode VARCHAR(10),
 SeqNum INTEGER
)
;

INSERT INTO "\memory\tmp1"(PostCode)
select addPostCode
FROM Addresses
ORDER BY 1
;

SELECT PostCode PC, Max(ID)+1 MaxID
INTO "\memory\tmp2"
FROM "\memory\tmp1"
GROUP BY 1
;

UPDATE "\memory\tmp1"
SET SeqNum = MaxID - ID
FROM "\memory\tmp1", "\memory\tmp2"
WHERE PostCode = PC
;

SELECT *
FROM "\memory\tmp1"
ORDER BY 2,3
-------------------------8<------------------------------

maybe you can adapt it for your data?

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 3 + EDB 1.00 build 6)

Alex,

<< I want to have a runcount that resets for every group, so for example:

Group A,  Rec 1   1
Group A, Rec 2    2
Group A, Rec 3,   3
Group B,  Rec 1   1
Group B, Rec 2    2
Group C,  Rec 1   1
Group C, Rec 2    2
Group C, Rec 3,   3

etc

How is this achievable in an SQL?   V3.30 >>

Try:

select grouping, RUNSUM(1)
from mytable
group by grouping

--
Tim Young
Elevate Software
www.elevatesoft.com