sequencing a column with group by

Question

sequencing a column with group by

Susane

SSC-Addicted

Points: 491
More actions
July 9, 2006 at 1:11 pm

#114669

hi
can somebody help me out with a simple query which i am not getting
there are three columns in a table with 100,000 records and i need to produce a sequence number to it

example :
table a
A B C
1 DD 5
1 DS 6
1 SDS EF
1 SDS 33
2 H HG
2 J DDD
2 D DV
3 V DSSD
3 K 67
3 6 77
3 8 88
A B C seq
1 DD 5 1
1 DS 6 2
1 SDS EF 3
1 SDS 33 4
2 H HG 1
2 J DDD 2
2 D DV 3
3 V DSSD 1
3 K 67 2
3 6 77 3
3 8 88 4
the result shoul look somthing like the above
can somebody please help me out
thanks in advance
susan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

If there are more than 100 items within a group, then just add appriopiate code. Also, if there is substantially less than 100 items in a group, just delete not needed code.

-- Prepare test data

--

-- Demonstration only. Not needed in your environment.

--

declare @tableA table (a int, b int, c int)

insert @tableA

select 1, 1, 1 union all

select 2, 2, 2 union all

select 3, 3, 3 union all

select 3, 3, 3

-- Do the work

SELECT w.a,

w.b,

w.c,

z.p

FROM (

SELECT a,

b,

c,

COUNT(*) n

FROM @tableA

GROUP BY a,

b,

c

) w

INNER JOIN (

SELECT 1 + b0.i + b1.i + b2.i + b3.i + b4.i + b5.i + b6.i p

FROM (SELECT 0 i UNION ALL SELECT 1) b0

CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1

CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2

CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3

CROSS JOIN (SELECT 0 i UNION ALL SELECT 16) b4

CROSS JOIN (SELECT 0 i UNION ALL SELECT 32) b5

CROSS JOIN (SELECT 0 i UNION ALL SELECT 64) b6

WHERE b0.i + b1.i + b2.i + b3.i + b4.i + b5.i + b6.i < 100

) z ON z.p <= w.n

ORDER BY w.a,

w.b,

w.c,

z.p

And for clarification, the "Prepare part" is not needed in your application. This is only for demonstration. You just need the "Do the work" part and you have to substitute the @tableA table with your real table name.

Good luck!

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

Why did you change the question?

It is better you start a new topic so other can learn from this too...

N 56°04'39.16"
E 12°55'05.25"

Susane SSC-Addicted Points: 491 More actions · Answer 3

thanks for your response i did post the query againg as v2

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

Yes, but you changed the testdata in this topic too...

This was the original test data in this topic.

A B C

1, 1, 1

2, 2, 2

3, 3, 3

And wanted output

A B C Seq

1, 1, 1 1

1, 1, 1 2

1, 1, 1 3

2, 2, 2 1

2, 2, 2 2

3, 3, 3 1

3, 3, 3 2

3, 3, 3 3

There is a quite a bit of difference calculating rank over three columns (easier) than just one column in you other topic (harder).

N 56°04'39.16"
E 12°55'05.25"

RyanRandall SSChampion Points: 13623 More actions · Answer 5

Here's a 'not recommended' way:

--data

create table #tableA (a int, b VARCHAR(3), c VARCHAR(4))

insert #tableA

select 1, 1, 1 union all

select 2, 2, 2 union all

select 3, 3, 3 union all

select 3, 3, 3

--calculation

alter table #tableA add i int

create clustered index abc on #tableA (a, b, c)

go

declare @i int, @previousA int

update #tableA set @i = case when a = @previousA then @i + 1 else 1 end, i = @i, @previousA = a

select * from #tableA

alter table #tableA drop column i

drop table #tableA

/*results

a b c i

----------- ---- ---- -----------

1 1 1 1

1 1 1 2

1 1 1 3

2 2 2 1

2 2 2 2

3 3 3 1

3 3 3 2

3 3 3 3

*/

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

A	B	C
1	DD	5
1	DS	6
1	SDS	EF
1	SDS	33
2	H	HG
2	J	DDD
2	D	DV
3	V	DSSD
3	K	67
3	6	77
3	8	88



A	B	C	seq
1	DD	5	1
1	DS	6	2
1	SDS	EF	3
1	SDS	33	4
2	H	HG	1
2	J	DDD	2
2	D	DV	3
3	V	DSSD	1
3	K	67	2
3	6	77	3
3	8	88	4