numbering my records based on a grouping 
Author Message
 numbering my records based on a grouping

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



Wed, 09 Aug 2006 00:31:07 GMT
 numbering my records based on a grouping

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
--



Wed, 09 Aug 2006 01:22:35 GMT
 numbering my records based on a grouping

Quote:
>> Any ideas <<

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!



Wed, 09 Aug 2006 02:09:47 GMT
 numbering my records based on a grouping
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


Quote:
> >> Any ideas <<

> 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!



Wed, 09 Aug 2006 08:30:14 GMT
 numbering my records based on a grouping
Quote:
>>this forum would have about 3 people.

Can you name them ;)?

--
Roji. P. Thomas
SQL Server Programmer

Quote:
> 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



> > >> Any ideas <<

> > 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!



Wed, 09 Aug 2006 09:57:22 GMT
 numbering my records based on a grouping
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......



Wed, 09 Aug 2006 15:21:05 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Top N Records Based on a Group

2. Grouping records based on a couple of fields.

3. grouping records based on *best* date

4. grouping records , and updating the first record in each group

5. grouping records, then updating the first record in each group

6. can FMP4.1 create files based on a record number

7. Sorting records based on number of matches in where clause

8. Grouping Records & Assigning Sequential Number

9. Number records in a grouping

10. Record number per group

11. Group-Wise Record Numbering

12. Find certain Number of Records / Print only certain number of records


 
Powered by phpBB® Forum Software