'SQL Row_number conditional

I need to row_number only Test_events in ('Math Test', 'Biology Test') in a separate column of my dataset with partitioning by student_id, session_id and ordering by Test_date asc, Test_order asc.

I tried to realize it in a column 'rnum' Code will be below. But I noticed that student 987 has incorrect rnum for 'Math Test' and 'Biology Test' there should be 1 and 2 rnum but I have 10 and 11. (for other students all correct).

How can I change my code to row_number only needed events in a separate column?

Needed numeration is in the column needed_rnum. the dataset:

student_id Test_type session_id Test_date Test_order rnum needed_rnum
987 First Test 1 2022-05-01 23:47:46.000 0 NULL NULL
987 First Test 1 2022-05-01 23:47:46.000 0 NULL NULL
987 Useful First Test 1 2022-05-01 23:47:46.000 1 NULL NULL
987 Useful First Test 1 2022-05-01 23:47:46.000 1 NULL NULL
987 Second Test 1 2022-05-01 23:48:42.000 4 NULL NULL
987 Screening Test NULL 2022-05-01 23:48:50.000 NULL NULL NULL
987 Second Test 1 2022-05-01 23:55:53.000 4 NULL NULL
987 Screening Test NULL 2022-05-01 23:56:01.000 NULL NULL NULL
987 History Test 1 2022-05-01 23:56:39.347 11 NULL NULL
987 English Test 1 2022-05-01 23:56:39.347 22 NULL NULL
987 Physics Test 1 2022-05-01 23:56:39.347 32 NULL NULL
987 Math Test 1 2022-05-01 23:56:43.080 50 10 1
987 Biology Test 1 2022-05-01 23:56:43.823 55 11 2
543 Math Test 1 2021-12-20 10:46:53.940 50 1 1
543 Biology Test 1 2021-12-20 11:19:54.173 55 2 2
543 Marked A+ 1 2021-12-21 13:17:33.100 500 NULL NULL
543 Math Test 2 2021-12-21 13:36:50.357 50 1 1
543 Biology Test 2 2021-12-23 10:47:39.267 55 2 2
543 Marked B+ 2 2022-01-14 10:41:10.993 110 NULL NULL
114 Math Test 1 2019-07-14 08:48:54.053 50 1 1
114 Biology Test NULL 2019-07-14 08:49:33.807 55 1 1
114 Biology Test 1 2019-07-14 08:49:34.153 55 2 2
114 Marked B+ 1 2019-09-13 01:05:47.467 110 NULL NULL 
SELECT  student_id, 
        Test_type, 
        session_id, 
        Test_date, 
        Test_order, 
        
     ,case when Test_type in (
        'Math Test', 'Biology Test'
        ) THEN row_number()over (partition by student_id, session_id order by Test_date asc, Test_order asc) 
        else NULL end as rnum
FROM tab1 t

enter image description here I am using MSSQL

sqlsql-serverwindow-functionspartitioning


Solution 1:[1]

You need to also partition by whether Test_type is one of your values

SELECT
  student_id, 
  Test_type, 
  session_id, 
  Test_date, 
  Test_order, 
  case when Test_type in (
        'Math Test', 'Biology Test'
        ) THEN
    row_number() over (partition by student_id, session_id, case when Test_type in ('Math Test', 'Biology Test') then 0 else 1 end
                       order by Test_date, Test_order) 
  end as rnum
FROM tab1 t

Note that ELSE NULL is the default, as is ASC

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Charlieface

Related Questions