Number grouped rows....please help!!

[arghhh]

Hi,

If i have the following table ( this is an example...)

Class Type
A A112
A A2
A A108
A A218
A A328
B B58
B B2
B B54
B B110
B B166
C C14
C C2
C C10

How do I number the rows so that each distinct row is numbered but within the class so that they expected output is:

Class Type output
A A112 1
A A2 2
A A108 3
A A218 4
A A328 5
B B58 1
B B2 2
B B54 3
B B110 4
B B166 5
C C14 1
C C2 2
C C10 3

Please please please help me!! I can't think on how to do this!

Thanks!!

[nedu]

Try using this if u r using SQL Server 2000 or lower versions . . .

CREATE TABLE #RowNumber ( RowNumber int IDENTITY (1, 1), Class char(9), Type1 char(10) )

INSERT #RowNumber (Class ,Type1)
SELECT Class ,Type1
FROM tablename group by Class ,Type1

Select * from #RowNumber


Thanks,
Nedu. M

[Jim Doherty]

Hi,

If i have the following table ( this is an example...)

Class Type
A A112
A A2
A A108
A A218
A A328
B B58
B B2
B B54
B B110
B B166
C C14
C C2
C C10

How do I number the rows so that each distinct row is numbered but within the class so that they expected output is:

Class Type output
A A112 1
A A2 2
A A108 3
A A218 4
A A328 5
B B58 1
B B2 2
B B54 3
B B110 4
B B166 5
C C14 1
C C2 2
C C10 3

Please please please help me!! I can't think on how to do this!

Thanks!!

Assuming your table is called tblClass try this

SELECT DISTINCT Class, Type,
                         (SELECT DISTINCT COUNT(*)
                            FROM         tblclass e2
                            WHERE     e2.class = e.class AND e2.type <= e.type) AS [Output]
FROM dbo.tblClass e

Regards

Jim :)

[arghhh]

Thank you so much Jim!

You've saved my query :-)