July 9, 2006 at 5:12 pm
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
July 9, 2006 at 6:59 pm
-- Prepare test data
--
-- Demonstration only. Not needed in your environment.
--
declare @tableA table (a int, b VARCHAR(3), c VARCHAR(4))
insert @tableA
select 1, 'DD', '5' union all
select 1, 'DS', '6' union all
select 1, 'SDS', 'EF' union all
select 1, 'SDS', '33' union all
select 2, 'H', 'HG' union all
select 2, 'J', 'DDD' union all
select 2, 'D', 'DV' union all
select 3, 'V', 'DSSD' union all
select 3, 'K', '67' union all
select 3, '6', '77' union all
select 3, '8', '88'
-- Do the work
SELECT q.A,
q.B,
q.C,
(
SELECT 1 + COUNT(DISTINCT B + CHAR(0) + C)
FROM @tableA z
WHERE z.A = q.A
AND z.B + CHAR(0) + z.C < q.B + CHAR(0) + q.C
) p
FROM @tableA q
ORDER BY q.A,
q.B,
q.C
N 56°04'39.16"
E 12°55'05.25"
July 9, 2006 at 7:15 pm
Or this one for handling ties.
-- Prepare test data
--
-- Demonstration only. Not needed in your environment.
--
declare @tableA table (a int, b VARCHAR(3), c VARCHAR(4))
insert @tableA
select 1, 'DD', '5' union all
select 1, 'DS', '6' union all
select 1, 'SDS', 'EF' union all
select 1, 'SDS', '33' union all
select 2, 'H', 'HG' union all
select 2, 'J', 'DDD' union all
select 2, 'D', 'DV' union all
select 3, 'V', 'DSSD' union all
select 3, 'V', 'DSSD' union all
select 3, 'K', '67' union all
select 3, '6', '77' union all
select 3, '8', '88'
-- Do the work
SELECT DISTINCT q.A,
q.B,
q.C,
(
SELECT 1 + COUNT(DISTINCT B + CHAR(0) + C)
FROM @tableA z
WHERE z.A = q.A
AND z.B + CHAR(0) + z.C < q.B + CHAR(0) + q.C
) + z.i p
FROM @tableA q
INNER JOIN (
SELECT t0.A,
t0.B,
t0.C,
t1.i
FROM (
SELECT A,
B,
C,
COUNT(*) n
FROM @tableA
GROUP BY A,
B,
C
)t0
INNER JOIN (
SELECT b0.i + b1.i + b2.i + b3.i + b4.i + b5.i + b6.i i
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
) t1 ON t1.i < t0.n
) z ON z.A = q.A AND z.B = q.B AND z.C = q.C
ORDER BY q.A,
q.B,
q.C
N 56°04'39.16"
E 12°55'05.25"
July 9, 2006 at 7:35 pm
Select Identity(int, 1,1) as ID, A, B,C
INTO #Table
FROM TheTable
ORDER BY A, B, C
Select T.A, T.B, T.C, COUNT(DT.ID) as Seq
FROM #Table T
INNER JOIN #Table DT ON T.A = DT.A AND T.ID >= DT.ID
GROUP BY T.A, T.B, T.C
_____________
Code for TallyGenerator
July 9, 2006 at 7:43 pm
Sergiy, try to run your code against this test data...
declare @tableA table (a int, b VARCHAR(3), c VARCHAR(4))
insert @tableA
select 1, 'DD', '5' union all
select 1, 'DS', '6' union all
select 1, 'SDS', 'EF' union all
select 1, 'SDS', '33' union all
select 2, 'H', 'HG' union all
select 2, 'J', 'DDD' union all
select 2, 'D', 'DV' union all
select 3, 'V', 'DSSD' union all
select 3, 'K', '67' union all
select 3, '6', '77' union all
select 3, '6', '77' union all
select 3, '8', '88'
Select Identity(int, 1,1) as ID, A, B,C
INTO #Table
FROM @tableA
ORDER BY A, B, C
Select T.A, T.B, T.C, COUNT(DT.ID) as Seq
FROM #Table T
INNER JOIN #Table DT ON T.A = DT.A AND T.ID >= DT.ID
GROUP BY T.A, T.B, T.C
DROP TABLE #Table
N 56°04'39.16"
E 12°55'05.25"
July 9, 2006 at 8:46 pm
it woks
thanks
July 10, 2006 at 5:37 am
Here's a 'not recommended' way:
--data
create table #tableA (a int, b VARCHAR(3), c VARCHAR(4))
insert #tableA
select 1, 'DD', '5' union all
select 1, 'DS', '6' union all
select 1, 'SDS', 'EF' union all
select 1, 'SDS', '33' union all
select 2, 'H', 'HG' union all
select 2, 'J', 'DDD' union all
select 2, 'D', 'DV' union all
select 3, 'V', 'DSSD' union all
select 3, 'V', 'DSSD' union all
select 3, 'K', '67' union all
select 3, '6', '77' union all
select 3, '8', '88'
go
--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 DD 5 1
1 DS 6 2
1 SDS 33 3
1 SDS EF 4
2 D DV 1
2 H HG 2
2 J DDD 3
3 6 77 1
3 8 88 2
3 K 67 3
3 V DSSD 4
3 V DSSD 5
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 11, 2006 at 8:23 am
Taking advantage of the first part of Sergiy's solution, but with different way of getting the sequence number (works well with sample data posted by Peter Larsson):
declare @tableA table (a int, b VARCHAR(3), c VARCHAR(4))
insert @tableA
select 1, 'DD', '5' union all
select 1, 'DS', '6' union all
select 1, 'SDS', 'EF' union all
select 1, 'SDS', '33' union all
select 2, 'H', 'HG' union all
select 2, 'J', 'DDD' union all
select 2, 'D', 'DV' union all
select 3, 'V', 'DSSD' union all
select 3, 'K', '67' union all
select 3, '6', '77' union all
select 3, '6', '77' union all
select 3, '8', '88'
Select Identity(int, 1,1) as ID, A, B,C
INTO #Table
FROM @tableA
ORDER BY A, B, C
Select T.A, T.B, T.C, T.ID-Q.minid+1 as Seq
FROM #Table T
INNER JOIN
(SELECT A, MIN(ID) as minid FROM #Table DT GROUP BY A) as Q
ON T.A = Q.A
DROP TABLE #Table
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply