Numbering of duplicates

K

kamil.jedrzejewski

Hi.
I need to do numbering of duplicate values and assign these numbers to
a table.
Example:
Table:
Field1
1
2
3
2
3
2
4
1

Reult I need:
Field1::Field2
1::1
2::1
3::1
2::2
3::2
2::3
4::1
1::2

where "::" is a field separator.
Additional assumptions:
- Field1 is a text field
- Updating is necessary (new data will appear in Field1 and should
also be numbered)

Thanks for help
Kamil
 
P

Pieter Wijnen

Something like
SELECT (SELECT COUNT(*)+1 FROM P1 WHERE P1.ID < P.ID AND P1.FIELD1=P.FIELD1)
AS ROWNUM, P.*
FROM P

HtH

Pieter
 
K

kamil.jedrzejewski

Something like
SELECT (SELECT COUNT(*)+1 FROM P1 WHERE P1.ID < P.ID AND P1.FIELD1=P.FIELD1)
AS ROWNUM, P.*
FROM P

HtH

Pieter









- Poka cytowany tekst -
Click to expand...

It works fine, thanks.
Now I want to assign these values to a field in table.
I tried something like
UPDATE MyTable
SET CounterField="..."
WHERE CounterField is Null

but it's not working. "..." is your SELECT statement.
What I'm doing wrong?

Regards,
Kamil
 
Top