CODEWITHSUNDEEP
sql-server-2005row-number
hungneox
hungneox

Reputation: 9829

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_id = c.category_id
  WHERE e.date >='5-1-2011' AND e.date<='5-31-2011'
  ) as ExpenseInfo
WHERE email='[email protected]'

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='[email protected]'
) AS ExpenseInfo
WHERE row_num>=1 and row_num<=20

Upvotes: 1

Views: 1302

Answers (2)

Filip De Vos
Filip De Vos

Reputation: 11908

The Where email = '[email protected]' 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 = '[email protected]'

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116110

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

Upvotes: 2

Related Questions