2

I'm using SQL Server 2012.

I want to set sequential numbers to Num field each time I execute the SQL with the rules as follows:

  • Grp order to be randomized.
  • ID field to be ascended.

Could anyone shed some light for me?

TABLE

    +-----+-----+----+
    | Num | Grp | ID |
    +-----+-----+----+ 
    |   0 |   1 |  1 | 
    |   0 |   1 |  2 | 
    |   0 |   1 |  3 | 
    |   0 |   2 |  4 | 
    |   0 |   2 |  5 |
    |   0 |   2 |  6 | 
    |   0 |   2 |  7 | 
    |   0 |   3 |  8 | 
    |   0 |   3 |  9 | 
    |   0 |   4 | 10 | 
    |   0 |   4 | 11 | 
    |   0 |   4 | 12 | 
    |   0 |   4 | 13 | 
    |   0 |   4 | 14 | 
    +-----+-----+----+

EXPECTED OUTPUT

    +-----+-----+----+ 
    | Num | Grp | ID |
    +-----+-----+----+
    |   1 |   3 |  8 |
    |   2 |   3 |  9 |
    |   3 |   1 |  1 |
    |   4 |   1 |  2 |
    |   5 |   1 |  3 |
    |   6 |   4 | 10 |
    |   7 |   4 | 11 |
    |   8 |   4 | 12 |
    |   9 |   4 | 13 |
    |  10 |   4 | 14 |
    |  11 |   2 |  4 |
    |  12 |   2 |  5 |
    |  13 |   2 |  6 |
    |  14 |   2 |  7 |
    +-----+-----+----+
Sarath KS
  • 15,816
  • 9
  • 67
  • 77
Hanericka
  • 59
  • 6

1 Answers1

1

SAMPLE TABLE

CREATE TABLE #TEMP(Num INT, Grp INT, ID INT)

   INSERT INTO #TEMP
    SELECT   0 ,   1 ,  1 
    UNION ALL
     SELECT    0 ,   1 ,  2 
     UNION ALL
    SELECT     0 ,   1 ,  3 
    UNION ALL
    SELECT     0 ,   2 ,  4
    UNION ALL
    SELECT     0 ,   2 ,  5
    UNION ALL
    SELECT     0 ,   2 ,  6 
    UNION ALL
    SELECT     0 ,   2 ,  7 
    UNION ALL
    SELECT     0 ,   3 ,  8
    UNION ALL
    SELECT     0 ,   3 ,  9 
    UNION ALL
    SELECT     0 ,   4 , 10
    UNION ALL
    SELECT     0 ,   4 , 11 
    UNION ALL
    SELECT     0 ,   4 , 12 
    UNION ALL
    SELECT     0 ,   4 , 13
    UNION ALL
    SELECT     0 ,   4 , 14

QUERY

;WITH CTE2 AS
(
    -- Now GRP will be ordered in random order using NEWID()
    SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) RNO,GRP 
    FROM 
    (
        -- Select unique GRP's
        SELECT DISTINCT GRP
        FROM #TEMP
    )TAB

)
SELECT ROW_NUMBER() OVER(ORDER BY rno,ID ASC) NUM,
C2.GRP,C1.ID
FROM CTE2 C2
JOIN #TEMP C1 ON C2.GRP=C1.GRP
order by rno,ID ASC

UPDATE

Here is the query to update the NUM field in your table with new row numbers.

;WITH CTE2 AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) RNO,GRP 
    FROM 
    (
        SELECT DISTINCT GRP
        FROM #TEMP
    )TAB

)
UPDATE #TEMP SET NUM = TAB.NUM
FROM
(
    SELECT ROW_NUMBER() OVER(ORDER BY rno,ID ASC) NUM,
    C2.GRP,C1.ID
    FROM CTE2 C2
    JOIN #TEMP C1 ON C2.GRP=C1.GRP
)TAB
WHERE #TEMP.GRP=TAB.GRP AND #TEMP.ID=TAB.ID


SELECT * FROM #TEMP
order by NUM,ID ASC
Sarath KS
  • 15,816
  • 9
  • 67
  • 77
  • Do u want to update the existing field in your table with new Num? @Hanericka – Sarath KS Jan 21 '15 at 04:19
  • Yes, I need Num field to be updated with sequential numbers. Hopefully you could show me how to write the update statement. – Hanericka Jan 21 '15 at 05:12
  • I really appreciate your kindnesses and the codes above. That worked perfectly! You shed light for me and I shed teas with joy to see the result on my screen. :) – Hanericka Jan 21 '15 at 05:32
  • Thank you very much. I am happy that you are fulfilled with your requirements. Feel free to ask for any change :) @Hanericka – Sarath KS Jan 21 '15 at 05:46