Hi, How can I create in a query a

[Cagliostro]

Hi,
How can I create in a query a counter grouped by some field?
For example
Field Counter
a 1
a 2
a 3
b 1
b 2
b 3
b 4
c 1
d 1
d 2 John Fill
ivfmd@mail.md

 

[hilbrink]

supposing you want to sum the counters:

SELECT field, SUM(counter)
FROM test
GROUP BY field

result:
a 6
b 10
c 1
d 3

 

[Cagliostro]

I do not need a sum, just a counter 1,2,3,4... In your query you show only a sum for each element. John Fill
ivfmd@mail.md

 

[hilbrink]

sorry i misunderstood.
Now i get the problem.
i'll think of an solution..

 

[apollo11]

Can't you just say:

Select field, counter from test group by field, counter

 

[Cagliostro]

counter is not a table column. I need to create it. I have many ideas, but I want know the best idea. John Fill
ivfmd@mail.md

 

[foxbox]

It looks like a variation of this: (you have to reset the counter every time the value of veld/ field is changing).


DECLARE Mijn_cursor CURSOR FOR
SELECT veld
from test
ORDER BY veld

DECLARE @PLAATS int
SET @PLAATS = 0

OPEN Mijn_cursor
FETCH NEXT FROM Mijn_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PLAATS = @PLAATS + 1
UPDATE test SET veld = @veld
WHERE CURRENT OF Mijn_cursor
FETCH NEXT FROM Mijn_cursor
END

CLOSE Mijn_cursor
DEALLOCATE Mijn_cursor


br
Gerard
(-:

 

[Cagliostro]

foxbox,
Thanks for your reply is helpfull but
using cursors is a not a so good idea, because is too slowly. I already have a better idea than using cursors.
My idea is following:

create table temp_table(x identity,field MyFieldsType)
insert into temp_table
select Field from MyTable order by field
select t_t.Field, (t.x - mt.x + 1) counter
(
select from Field, min(x) x from temp_table group by Field
) t inner join temp_table t_t on
t.Field = t_t.field

What I want is a some thing if possible without cursors and temporary tables. If it is possible in only one step. John Fill
ivfmd@mail.md