Creating Sequence Numbers
-
I am trying to create a sequence number column. I am creating a table using joins that results in the following type of format
ID1 ID2
85678 6548
85678 6549
85678 6550
85678 6551
85755 6984
85755 6985
85755 6986
86205 7011
86205 7012
I want to add a column so it looks like:
ID1 ID2 Seq#
85678 6548 1
85678 6549 2
85678 6550 3
85678 6551 4
85755 6984 1
85755 6985 2
85755 6986 3
86205 7011 1
86205 7012 2
How do I create the Seq# column?
Thanx
-
Is the Seq# permanent in the table or just in a select.
How much data are you talking about (rows).
-
The current select pulls about 100 000 records, but I doubt the seq# would ever be more than 100.
The application is:
You have a sales order number 85678. That sales order has 4 line items on it that have a hidden ids of 6548, 6549, 6550, 6551. I need to create a column in my select that gives another number to those line items. Order 85678 / Line Item 6548 is Seq# 1. Order 85678 / Line Item 6549 is Seq# 2. etc
Thanx
-
Sorry.. I missed the second part...
It is only in the select..
-
Why do you need to do that server side? It's very easy to do that with a reporting tool or in any other languages.
It can be done server side but the cost of doing this for 100K rows is high. Once is not a problem, but if you need to run this every few minutes, then I strongly suggest you do it client side.
-
I'd like to say that I am doing it cause of excessive need, but I cannot.
I am going by the specs someone else drew up and trying to match it. This was one of their requirements. I am creating a view that will only be accessed once a day for a data dump.
-
USE MASTER
GO
CREATE TABLE #Work (ID INT, Name SysName, colid smallint, rowid smallint)
INSERT INTO #Work (ID, Name, colid)
SELECT ID, Name, Colid FROM dbo.SysColumns ORDER BY ID, Colid
DECLARE @rowid AS INT
DECLARE @LastID AS INT
DECLARE @CurrentID AS INT
SET @LastID = -2
SET @CurrentID = -1
SET @RowID = 0
UPDATE #Work
SET
rowid = @rowid
, @LastID = @CurrentID
, @CurrentID = id
, @rowid = CASE WHEN @LastID = @CurrentID THEN @rowid ELSE 0 END + 1
SELECT ID, Name, Colid, RowID FROM #Work
DROP TABLE #Work
--takes 2 second to process 5K rows on my small machine.
--So 100K rows on a prod server shouldn't be a major hit... especially once a day
-
PS... you obviously have to use a SP to run this
.The code to make the equivalent in a view would really kill the server because it would require a triangular join. Here's an exemple (not gonna be as bad in your case but you'd get the idea).
--shoot 100k rows in a temp table
SELECT TOP 100000 IDENTITY(BIGINT, 1, 1) AS a into #x FROM master.dbo.SysColumns C1 CROSS JOIN master.dbo.SysColumns C2
--Count the
SELECT COUNT(*) + SUM(a-1) AS RowsProcessed FROM #x
--5 000 050 000
DROP TABLE #X
This is one of the very rare cases where a cursor is more efficient than any set based solution (apart from the one I just presented... they should be pretty close).
-
THanx for your help.
I am going to have to look at this and figure it out.
-
Here's another way if you have a static numbers table. If you don't, this may be a good time to create one!
DECLARE @table TABLE (ID1 int,ID2 int)
INSERT INTO @table
SELECT 85678, 6548 UNION ALL
SELECT 85678, 6549 UNION ALL
SELECT 85678, 6550 UNION ALL
SELECT 85678, 6551 UNION ALL
SELECT 85755, 6984 UNION ALL
SELECT 85755, 6985 UNION ALL
SELECT 85755, 6986 UNION ALL
SELECT 86205, 7011 UNION ALL
SELECT 86205, 7012
SELECT ID1,
Lowest + Num as ID2,
Num
FROM (
SELECT ID1,
(MIN(ID2) - 1) as Lowest,
COUNT(*) as [Count]
FROM @table t
GROUP BY ID1
) t
INNER JOIN dbo.Numbers N
ON N.Num BETWEEN 0 AND t.[Count]
-
Wow that's a nice new approach.
The only downside I can see is that the 2nd id must have an unbroken sequence to make this work... and be some sort of numeric. Other that that
.Thanx for sharing this one.
-
Thanks Remi.
Yes, you are correct. The ID2 column must not have any gaps for this one to work and ID2 must be numeric. Thanks for pointing that out. I guess I assumed that it would. I know that you are fully aware of the usage of numbers tables, but I have yet to see this approach in other posts. Numbers tables can be used to 'expand' aggregate values in sequence. This can be handy any time a sequence needs to be generated. Also, this can be used without the ID2 column logic just to give a sequence to a grouped set of data.
-
Interesting, I was just working on this problem for our local SQL server Users group,
Here is what I've got, if it helps:
CREATE TABLE #Results ( ID INT IDENTITY(1,1), Score INT , Sport VARCHAR(20), Team VARCHAR(50) ) INSERT #Results(Score, Sport,team) VALUES(86, 'Tennis', 'Rockin'' Rackets') INSERT #Results(Score, Sport,team) VALUES(84, 'Tennis', 'Love All, Serve all') INSERT #Results(Score, Sport,team) VALUES(45, 'Tennis', 'The Racketeers') INSERT #Results(Score, Sport,team) VALUES(798, 'Bowling', 'I can''t believe it''s not gutter') INSERT #Results(Score, Sport,team) VALUES(720, 'Bowling', 'Spare Me') INSERT #Results(Score, Sport,team) VALUES(35, 'Football', 'The Roughnecks') INSERT #Results(Score, Sport,team) VALUES(31, 'Football', 'Pigskin Pride') --------------------------- --Using a self join & GROUP BY query --Does not work with duplicates select t1.Score, t1.Sport, min(t1.team) as team, count(*) AS rank from #Results t1 INNER JOIN #Results t2 ON t2.Score >= t1.Score -- Order BY Col (use = for ascending AND t2.Sport = t1.Sport -- Partition BY Col GROUP BY t1.Score, t1.Sport ORDER BY t1.sport, rank --Using a corelated subquery SELECT t2.Score, t2.Sport, team, ( SELECT COUNT(*) FROM #Results t1 WHERE t1.Score >= t2.Score -- Order BY Col (use = for ascending AND t1.id <= t2.id -- Supplimental Order by (to account for duplicates) AND t1.Sport = t2.Sport -- Partition BY Col ) AS rank from #Results t2 ORDER BY t2.sport, rank GO drop table #ResultsSQL guy and Houston Magician
-
PS. the line:
AND t1.id <= t2.id -- Supplimental Order by (to account for duplicates)
is optional if your order by column is unique
SQL guy and Houston Magician
-
One more PS... Just in case, If you are running SQL2005, use the ROW_NUMBER function.
ROW_NUMBER OVER(PARTITION BY ID1 ORDER BY ID2) AS SEQ
SQL guy and Houston Magician
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply