ROW_NUMBER() return values are not consistent

I used ROW_NUM() function for paging in ASP.NET

SELECT row_num, expense_id,email, reason, amount,date,category_name,is_income
FROM
  (
  SELECT e.expense_id,e.email, e.reason, e.amount,e.date,c.category_name,e.is_income, 
  ROW_NUMBER() OVER(ORDER BY e.date DESC,e.expense_id) as row_num
  FROM Expense e
  JOIN Category c ON e.category开发者_Python百科_id = c.category_id
  WHERE e.date >='5-1-2011' AND e.date<='5-31-2011'
  ) as ExpenseInfo
WHERE email='sample@domain.com'

But it returned an inconsistent list of row_num (such as 1,3,4... or 2,3,4...). How can I solve this problem?

Thanks in advance.

I have solved my problem

SELECT RowNum, expense_id, email, reason, amount, date, category_name, is_income
FROM
(
SELECT e.expense_id, e.email, e.reason, e.amount, e.date, c.category_name, e.is_income, Row_Number() OVER(ORDER BY date DESC) as row_num 
FROM Expense e JOIN Category c 
  ON e.category_id = c.category_id
WHERE e.date >='5-1-2011' AND e.date<='5-31-2011' AND e.email='sample@domain.com'
) AS ExpenseInfo
WHERE row_num>=1 and row_num<=20

---

Add the ROW_NUMBER in the main query. When you add it in the subquery, first the numbers are generated, and afterwards you're filtering the records. When you generate the row numbers on the final result, you won't have that problem.

select
  s.*,
  row_number() over(order by s.date desc, s.expense_id) as row_num
from
  (
   /*YourQueryWithAllItsConditions*/
  ) s

---

The Where email = 'sample@domain.com' is filtering out certain rows. For what you are doing here, you don't need the "outer" query. Just wrap it all together.

 SELECT ROW_NUMBER() OVER(ORDER BY e.date DESC, e.expense_id) as row_num
       , e.expense_id
       , e.email
       , e.reason
       , e.amount
       , e.date
       , c.category_name
       , e.is_income
  FROM Expense e
  JOIN Category c 
    ON e.category_id = c.category_id
  WHERE e.date >='5-1-2011' 
    AND e.date <='5-31-2011'
    AND e.email = 'sample@domain.com'