SQL Server Order By GroupID Random but asc ID in each GroupID

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 |
    +-----+-----+----+

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 

• Click here to view result

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

• Click here to view the result