title: How to have where clause on row_number within the same select statement?
body: <p>I'm not able to use where condition on the row number within the same select statement. Results are not consistent if I use a different select statement for applying condition over the rownumber...</p>
  
  <pre><code> SELECT TOP (@lastrow - 1) c.totalRows
          ,c.ae_effective_enrollment_id
          ,c.[user_id]
          ,c.login_name
          ,c.first_name
          ,c.last_name
          ,cm.courseware_title
          ,cm.courseware_code
          ,@courseware_id assetId
          ,c.enrollment_status_id
          ,CASE 
              WHEN c.enrollment_status_id = 2
                  AND c.is_self_enrolled = 0
                  THEN 'Admin-' + s.description
              WHEN c.enrollment_status_id = 2
                  AND c.is_self_enrolled = 1
                  THEN 'Self-' + s.description
              ELSE s.description
              END AS enrollmentStatus
          ,c.is_group
          ,CASE 
              WHEN c.is_self_enrolled = 0
                  THEN 1
              ELSE 0
              END is_admin
          ,CASE 
              WHEN c.auma_is_assigned = 1
                  THEN 'Admin-assigned'
              WHEN c.auma_is_assigned = 0
                  THEN 'Self-assigned'
              ELSE 'No-My-Plan'
              END AS myplanStatus
          , master_assignment_id
          ,ROW_NUMBER() over(partition by cm.courseware_id,c.user_id order by c.is_self_enrolled)as check_row
      FROM enrollmentCTE c
      INNER JOIN dbo.courseware_master cm ON cm.courseware_id = @courseware_id
      LEFT JOIN @statuscodes s ON s.id = c.enrollment_status_id
      WHERE check_row=1 and 
      enrollment_status_id&lt;&gt;4 and
      rownumber &gt; @firstrow
          AND rownumber &lt; @lastrow
      ORDER BY rownumber 
  </code></pre>
  
  <p><code>check_row</code> here is not recognised. Please help </p>