Add ROW_ID group by Name

• adonetok

  SSCrazy Eights

  Points: 8395

  More actions

  February 9, 2011 at 1:13 pm

  #97797

  How to add ROW_ID as data below?

  Since SQL 2000 do not have ROW_NUMBER function. It make it hard for me.

  Name ROW_ID

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

  AAA 1

  AAA 2

  BBB 1

  BBB 2

  BBB 3

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 9, 2011 at 1:29 pm

  #1284805

  if you are stuck with SLq 2000, you have to use a temp table; there's no other way around it.

  here's a fine example from Jeff Moden that i copied years ago:

  --===== Create a test table.

  -- This is NOT part of the solution.

  IF OBJECT_ID('TempDB..#YourTable') IS NOT NULL

  DROP TABLE #YourTable

  CREATE TABLE #YourTable

  (

  AccountNbr INT,

  CreateDate DATETIME

  )

  --===== Populate the test table with data.

  -- This is NOT part of the solution.

  INSERT INTO #YourTable

  (AccountNbr,CreateDate)

  SELECT '59961','01/05/09' UNION ALL

  SELECT '59961','01/06/09' UNION ALL

  SELECT '59961','01/07/09' UNION ALL

  SELECT '32187','01/05/09' UNION ALL

  SELECT '32187','01/06/09' UNION ALL

  SELECT '22195','01/10/09' UNION ALL

  SELECT '22195','01/12/09' UNION ALL

  SELECT '22195','01/13/09' UNION ALL

  SELECT '22195','01/15/09' UNION ALL

  SELECT '69248','01/11/09' UNION ALL

  SELECT '69248','01/12/09'

  --===== Instead, use the following method which will solve a

  -- million rows in about 7 seconds.

  IF OBJECT_ID('TempDB..#SeqWork') IS NOT NULL

  DROP TABLE #SeqWork

  DECLARE @PrevAccountNbr INT,

  -- @PrevCreateDate DATETIME,

  @PrevSeq INT

  SELECT ISNULL(AccountNbr,0) AS AccountNbr,

  ISNULL(CreateDate,0) AS CreateDate,

  CAST(0 AS INT) AS Seq

  INTO #SeqWork

  FROM #yourtable

  ORDER BY AccountNbr, CreateDate

  ALTER TABLE #SeqWork

  ADD PRIMARY KEY CLUSTERED (AccountNbr, CreateDate)

  UPDATE #SeqWork

  SET @PrevSeq = Seq = CASE WHEN AccountNbr = @PrevAccountNbr THEN @PrevSeq+1 ELSE 1 END,

  @PrevAccountNbr = AccountNbr

  FROM #SeqWork WITH(INDEX(0),TABLOCKX)

  SELECT *

  FROM #SeqWork

  ORDER BY AccountNbr, CreateDate

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• adonetok

  SSCrazy Eights

  Points: 8395

  More actions

  February 9, 2011 at 1:38 pm

  #1284810

  Thanks, it works for me.