You could try something like:
WHEN grade= 'fail' then 0
ELSE row_number() over ( partition by grade order by module )
end as rownumber
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.
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'
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...
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.
Okay User, Then mark my answer as correct one.So it'll help others in future.