5 Replies Latest reply: Aug 2, 2013 8:43 AM by Soofi RSS

    Problems with Row_Number function

    user8655468

      I am having problems with the Row_Number function. I am using it to assign row numbers to records where a student has a grade of pass on a module and excluding failed modules (I want it to show a 0 as row number for failed modules). The problem is that when I try to use a condition, the report still assigns a row number to a failed module though it doesnt display it (it displays a 0 which I wanted it to show). The results appear as follows:

       

      Row numberModuleGrade
      1ModuleAPass
      2ModuleBPass
      0ModuleCFail
      4 (instead of 3)ModuleDPass

       

      How can I make it skip assigning a row number to all failed modules? Please assist.

       

      Thanks.

        • 1. Re: Problems with Row_Number function
          jaasteij

          You could try something like:

           

          ,CASE

               WHEN grade= 'fail' then 0

               ELSE row_number() over ( partition by grade order by module )

          end as rownumber

          • 2. Re: Problems with Row_Number function
            user8655468

            I have tried using the CASE statement and it gave me the same results as shown in the table above and also the CASE statement was showing an error on the report Data Model (Reports Builder) because I have linked queries. Is there any other function that can be used to get row numbers OR what do I need to include on the Row_number function to achieve what I want? Thanks.

            • 3. Re: Problems with Row_Number function
              Soofi

              It is tricky to use rownum. Because if we generate sequence no by select max(column)+1  from table name ..we can match the where condition and assign the incremented value to the PASS condition.

               

              On the other hand rownum generates a sequence while it is fetching the records one by one so obviously you get the above result only So i will give you an idea to fetch the records of 'PASS' condition first.

               

              Take a look at the below query and alter it and use it on your report.

               

              select a,b,c from (

              select  0 a,module b, grade c from table where grade = 'FAIL'

              union all

              select rownum a,module b, grade c from table where grade = 'PASS' order by b)

              order by a

               

              So the above query generates row number only for the PASS condition.

               

              Hope this helps...

               

              Regards,

              Soofi.

              • 4. Re: Problems with Row_Number function
                user8655468

                Thanks a lot Soofi. I have done the query amendments as per your suggestion, i.e. did a union of the failed and passed modules (using the row_number function only on the Passed modules). Thanks for the solution.

                 


                • 5. Re: Problems with Row_Number function
                  Soofi

                  Okay User, Then mark my answer as correct one.So it'll help others in future.

                   

                  Regards,

                  Soofi