Example....
I have 8 records with 2 fields
     Name             Type
FRED                 A
FRED                 A
SAM                  R
JOHN                 N
JOE                   R
JOE                   R
BILL                  N
TOM                 R

I want to number records by adding another field. But I want a SQL
statement to populate the new field by counting the occurances of records with the
same name and type.
This is my result, (but I don't know how to get it)

I have 8 records with 2 fields
     Name             Type         Cntr
FRED                 A                 1
FRED                 A                 2
SAM                  R                 1
JOHN                 N                 1
JOE                   R                 1
JOE                   R                 2
BILL                  N                 1
TOM                 R                 1

Any ideas

Does this table have a primary key? If not, then adding an incrementing row
count just seems likely to add to your problems. Every table should have a
key. Create and populate a new table like this:

CREATE TABLE NewTable (name VARCHAR(10), type CHAR(1), cnt INTEGER NOT NULL
CHECK (cnt>0), PRIMARY KEY (name,type))

INSERT INTO NewTable (name, type, cnt)
    SELECT name, type, COUNT(*)
        FROM SomeTable
        GROUP BY name, type

If you do have a key then you can create the Cntr column either with an
UPDATE:

UPDATE SomeTable
 SET cntr =
  (SELECT COUNT(*)
   FROM SomeTable AS T
   WHERE name = SomeTable.name
    AND keycol <= SomeTable.keycol)

Or in a SELECT statement:

SELECT name, type,
  (SELECT COUNT(*)
   FROM SomeTable AS T
   WHERE name = SomeTable.name
    AND keycol <= SomeTable.keycol)
 FROM SomeTable

--
David Portas
SQL Server MVP
--

Learn SQL and RDBMS, so that your tables will have keys in the future;
right now you are a danger to your employer and should not be allowed
near a database.  Rows are not records; fields are not columns; tables
are not files; there is no sequential access or ordering in an RDBMS, so
"first", "next" and "last" are totally meaningless.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Everyone has to start somewhere, Joe.  If everyone waited until they were
experts before trying to program databases this forum would have about 3
people.

--
JT Lovell

Can you name them ;)?

--
Roji. P. Thomas
SQL Server Programmer

Joe
For your information I have been using SQL on and off since 1990. So you could say I am from the old school when rows were records and fields were columns.

Unfortunately not everybody knows everything and sometimes we need help. Luckily there are people out there who are willing to help those of us who need it. And not put us down for asking a question.

I hope you are not a teacher or a manager because that is no way to treat your subordinates.

PS. My table does have a primary key......