1. Registered User
Join Date
Jan 2002
Posts
16

I have a table that looks like this:
c1 c2
1 a
1 b
1 c
2 a
2 b
2 c
2 d
3 a
3 b
3 c
3 d
3 e

I want a result set that looks like this:
c1 c2 c3
1 a 1
1 b 2
1 c 3
2 a 1
2 b 2
2 c 3
2 d 4
3 a 1
3 b 2
3 c 3
3 d 4
3 e 5

Basically, grouping/numbering the rows by column c1. The number of distinct c1 columns and distinct c1,c2 columns is varying.

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
I'd use something like:
Code:
```CREATE TABLE tGeorgio (
c1		INT		NOT NULL
,  c2		CHAR(1)		NOT NULL
PRIMARY KEY (c1, c2)
)
GO

INSERT INTO tGeorgio (c1, c2)
SELECT           1, 'a'
UNION ALL SELECT 1, 'b'
UNION ALL SELECT 1, 'c'
UNION ALL SELECT 2, 'a'
UNION ALL SELECT 2, 'b'
UNION ALL SELECT 2, 'c'
UNION ALL SELECT 2, 'd'
UNION ALL SELECT 3, 'a'
UNION ALL SELECT 3, 'b'
UNION ALL SELECT 3, 'c'
UNION ALL SELECT 3, 'd'
UNION ALL SELECT 3, 'e'

SELECT c1, c2
, (SELECT Count(*)
FROM tGeorgio AS b
WHERE  b.c1 = a.c1
AND b.c2 <= a.c2) AS c3
FROM tGeorgio AS a
ORDER BY 1, 2, 3```
-PatP

3. Registered User
Join Date
Oct 2003
Posts
357
Hi,
Other possible way of Pat Phelan method is
Code:
```select t1.c1,t1.c2,count(*) as count from tGeorgio t1, tGeorgio t2
where t1.c1=t2.c1 and t1.c2>=t2.c2  group by t1.c1,t1.c2```
Last edited by Madhivanan; 11-30-04 at 01:01.

4. Registered User
Join Date
Jan 2002
Posts
16
Thanks. The mental block has ben lifted.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•