Specific Condition of a column based ROW_NUMBER in SQL Server-CodePudding

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.

CodePudding user response：

You can compute a running sum that increments when a repeated <Id, Type, Name> is found. Then you can pick the first partition with TOP 1 WITH TIES.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Name, Type, ID ORDER BY StartTime)-1 AS rn 
    FROM tab
)
SELECT TOP 1 WITH TIES Id, Type, Name 
  FROM cte
ORDER BY SUM(rn) OVER(ORDER BY StartTime)

Check the demo here.

Note: I'm guessing "StartTime" is associated to datetime type. In the fiddle appears as an integer for the sake of simplicity, should work with datetime as well.

CodePudding user response：

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