July 9, 2006 at 2:27 pm
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 1, 1, 1 union all
select 1, 1, 1 union all
select 2, 2, 2 union all
select 2, 2, 2 union all
select 3, 3, 3 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"
July 9, 2006 at 5:20 pm
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"
July 9, 2006 at 6:14 pm
thanks for your response i did post the query againg as v2
July 9, 2006 at 7:49 pm
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
1, 1, 1
1, 1, 1
2, 2, 2
2, 2, 2
3, 3, 3
3, 3, 3
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"
July 10, 2006 at 5:41 am
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 1, 1, 1 union all
select 1, 1, 1 union all
select 2, 2, 2 union all
select 2, 2, 2 union all
select 3, 3, 3 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.
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply