How to have where clause on row_number within the same select statement?

Question

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...

 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<>4 and
    rownumber > @firstrow
        AND rownumber < @lastrow
    ORDER BY rownumber 

check_row here is not recognised. Please help

Answer 1

SQL order of execution.

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

the check_row alias was made in the select part so it doesn't exist yet in the context

EDIT done some testing. can't seem to get it right. as a temporary solution you could attempt to put the

ROW_NUMBER() over(...

in the where clause aswell

EDIT: another option from the MSDN website is

Returning a subset of rows

The following example calculates row numbers for all rows in the SalesOrderHeader table in the order of the OrderDate and returns only rows 50 to 60 inclusive.

USE AdventureWorks2012;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber
    FROM Sales.SalesOrderHeader 
) 
SELECT SalesOrderID, OrderDate, RowNumber  
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;