Specific Condition of a column based ROW_NUMBER in SQL Server

Question

A select query needs to show all records except a first record of a column having a specific condition

Current Data:

ID  Type    Name
123 0   Sathiya
123 1   Kumar
123 NULL    Mohan
123 6   Ranjith
124 0   Sathiya
123 0   John
125 6   Albert
123 0   Sathiya
124 0   Kumar
124 0   Joseph

Expected Data:

ID  Type    Name
123 0   Sathiya
123 1   Kumar
123 NULL    Mohan
123 6   Ranjith
124 0   Sathiya
125 6   Albert

Used Query:

SELECT
ROW_NUMBER() PARTITION BY ID,Type ORDER BY StartTime ASC AS RN,
*
INTO #temp1
FROM TABLE

SELECT 
*
FROM #temp1
WHERE RN>1

But this query will not suffice to provide the required data as the RN will be partition for all the Types but I need to get the RN only for Type 0 for same CallID. Except first Type=0 for same CallID, reset of the Type=0 for the same CallID needs to be neglected in the final select.

Any clues or ideas are much appreciated.

Answer 1

So I didn't get an idea to do it in direct method hence I followed the Union.

Selected first set of data with Type!=0 and Second set of data with Type=0 with ROW_NUMBER() OVER(PARTITION BY Name, Type, ID ORDER BY StartTime) which provides 1,2,3 for only Type 0. Then putting RN=1 gives the expected result.

SELECT
*
INTO #tempTypeNo0
FROM TABLE
WHERE Type!=0

SELECT
ROW_NUMBER() PARTITION BY ID,Type ORDER BY StartTime ASC AS RN,
*
INTO #tempType0
FROM TABLE
WHERE Type=0

SELECT *
FROM
(
SELECT ID,Type,Name FROM tempTypeNo0
UNION
SELECT ID,Type,Name FROM tempType0 WHERE RN=1

)a