line numbering
-
I need to number lines for which I can not use the identity column to populate.
Example
Doc No Line No
1 1
1 2
2 1
2 2
etc... The doc no I have but I need to populate the Line No. I know I could build a cursor for this but this could grow to hundreds of thousand of doc no i would be processing. I am newbie so be gentle
-
This is something that you should do at the client if this is for presentation purpusses only.
Why are you trying to do this?
-
I am trying to massage tables from a vendors file and map it over to a system that I support that requires line numbering.
-
I couldn't figure out a way to do this without an identity, but here are my results
create table #t (ids int identity(1,1), value int)
insert into #t
select 1
union all
select 1
union all
select 2
union all
select 3
union all
select 3
union all
select 3
SELECT COUNT(*) AS NO, v.value
FROM #t v CROSS JOIN #t a
WHERE a.value=v.value
and v.ids <= a.ids
GROUP BY v.value,v.ids
ORDER BY v.value, 1, v.ids
set nocount off
drop table #t
I agree with Remi, this would be better suited with a client tool.
Thanks
Greg
-
Check out my first post on this Thread (final update from (select))
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=189202
-
/* Just for the heck of doing it using a cursor. */
/* Data */
SET NOCOUNT ON
DECLARE @Document TABLE
(
DocumentID INT,
DoumentDetails VARCHAR(25)
)
INSERT @Document VALUES (1, 'Details 1 A')
INSERT @Document VALUES (1, 'Details 1 B')
INSERT @Document VALUES (1, 'Details 1 C')
INSERT @Document VALUES (2, 'Details 2 A')
INSERT @Document VALUES (2, 'Details 2 B')
INSERT @Document VALUES (2, 'Details 2 C')
INSERT @Document VALUES (2, 'Details 2 D')
INSERT @Document VALUES (3, 'Details 3 A')
INSERT @Document VALUES (4, 'Details 4 A')
INSERT @Document VALUES (4, 'Details 4 B')
INSERT @Document VALUES (4, 'Details 4 C')
INSERT @Document VALUES (5, 'Details 5 A')
/* Solution */
DECLARE @Results TABLE
(
DocumentID INT,
DocumentLineNo INT,
DoumentDetails VARCHAR(25)
)
DECLARE @DocumentID INT
DECLARE myCursor CURSOR FOR
SELECT DISTINCT DocumentID FROM @Document
OPEN myCursor
FETCH NEXT FROM myCursor INTO @DocumentID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT @Results
SELECT A.DocumentID, Rank = count(*), A.DoumentDetails
FROM @Document A
JOIN @Document B
ON CONVERT(VARCHAR,A.DocumentID) + A.DoumentDetails >= CONVERT(VARCHAR,B.DocumentID) + B.DoumentDetails
AND A.DocumentID = @DocumentID AND B.DocumentID = @DocumentID
GROUP BY A.DocumentID, A.DoumentDetails
ORDER BY 1
FETCH NEXT FROM myCursor INTO @DocumentID
END
CLOSE myCursor
DEALLOCATE myCursor
SELECT * FROM @Results
ORDER BY 1, 2
Regards,
gova -
Just for the heck of shooting you down to make sure he never uses this crappy (performing) solution.
-
You said to go easy, so I will try to describe the solution in words first.
To create line numbering, you first have to decide how to assign line numbers (how do you know which line is number 1?). Then using these conditions you can assign unique number (let's call it RNUMB) to every row, in correct order... and then calculate the line number for each line of a document by taking the RNUMB of this row and subtracting the lowest RNUMB with the same document number. Don't forget to add 1 to the result, if you want to number lines from 1 (not from 0). Or you can calculate the line number the way Greg mentioned, that is by counting, how many lines there are, which are classified as less than the actual line. Especially in this case, you need to be sure every single row is unique in the ordering criteria... if you have several rows with the same ordering value, then you have a problem.
And now an example:
/*OK, so you have a table input_data, and you need to number lines somehow...let's say alphabetically, depending on the text.*/
CREATE TABLE input_data(doc_no INT NOT NULL, sometext VARCHAR(256))
INSERT INTO input_data values (1,'blabla')
INSERT INTO input_data values (1,'heyboy')
INSERT INTO input_data values (1,'antares')
INSERT INTO input_data values (1,'88856')
INSERT INTO input_data values (2,'pump')
INSERT INTO input_data values (3,'John')
INSERT INTO input_data values (3,'Carl')
INSERT INTO input_data values (3,'Hugo')
/*make a temporary table to assign unigue IDENTITY number*/
CREATE TABLE #ord (RNUMB INT IDENTITY (1,1), doc_no INT NOT NULL, line_no INT, sometext VARCHAR(256))
/*insert data ordered alphabethically inside each document*/
INSERT INTO #ord(doc_no, sometext)
SELECT * FROM input_data ORDER BY doc_no, sometext
/*now assign the line numbers*/
UPDATE o
SET line_no = RNUMB - (select min(RNUMB) from #ord where doc_no = o.doc_no) + 1
FROM #ord o
SELECT * from #ord
DROP TABLE input_data
DROP TABLE #ord
HTH, Vladan
-
May not necessarily apply to question listed, but to dynamically number rows, you can do self join.
Reference,
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q186133
Good examples
-
Next SQL will contain OLAP function ROW_NUMBER()
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply