Results 1 to 4 of 4

Thread: sequence number

  1. #1
    Join Date
    Jan 2002
    Posts
    77

    Unanswered: sequence number

    suppose I have the following table grouped by memid

    memid dx
    3 455
    3 322
    3 232
    4 33
    4 3434

    I want to attach sequence number for each unique value of dx per memid as
    below

    memid dx SEQ
    3 455 1
    3 322 2
    3 232 3
    4 33 1
    4 3434 2

    I am using a cursor right now and it takes a lot of time if my table is large.
    Is there a more efficient way of doing this.

    Thanks much.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How about:
    Code:
    --  ptp  20071003  See http://www.dbforums.com/showthread.php?t=1623021
    
    SELECT 3 AS memid, 455 AS dx		-- Just set up the data
       INTO #foo
       UNION SELECT 3, 322	UNION SELECT 3, 232 
       UNION SELECT 4, 33	UNION SELECT 4, 3434
    
    SELECT a.memid, a.dx			-- Return the requested results
    ,  (SELECT Count(*)
          FROM #foo AS b
          WHERE  b.memid = a.memid
             AND b.dx <= a.dx)
       FROM #foo AS a
    
    DROP TABLE #foo
    -PatP

  3. #3
    Join Date
    Feb 2007
    Posts
    62
    I think this will be more efficient in SQL Server 2005.

    select memid, dx,
    row_number() over(partition by memid order by memid) seq
    from
    (
    SELECT 3 AS memid, 455 AS dx -- Just set up the data
    UNION SELECT 3, 322 UNION SELECT 3, 232
    UNION SELECT 4, 33 UNION SELECT 4, 3434
    ) as x


    This is a bizarre requirement. Any chance of an explanation of why you might want to do this?

  4. #4
    Join Date
    Apr 2007
    Posts
    183
    partition by memid order by dx desc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •