This discussion is archived
5 Replies Latest reply: Aug 2, 2013 6:43 AM by Soofi RSS

Problems with Row_Number function

user8655468 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

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

     

    Regards,

    Soofi

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points