New to ROW_NUMBER() function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

New to ROW_NUMBER() function

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2011-03-27 : 21:32:36

Hi

My goal is to return records 1-50 on PDF page, 51-100 on the next page etc. I've never used ROW_Number before and I thought this code would return the first 50 records, and then I would change the StartRow and EndRow parameters for the next 50 etc. but it only returns 3 records. A simple SELECT statement of WHERE IMF_ID IN (51,52) returns over 7,000 records. I guess I just understand the proper use of ROW_Number(). Thanks

USE IMF_ED
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 1
SET @EndRow = 50

SELECT Borrower_FName,
Borrower_LName,
DateClosed,
DebtID,
IMF_Type,
AG,
IMF_ID
FROM (
SELECT PC.Borrower_FName, PC.Borrower_LName, PC.DateClosed, PC.DebtID, PC.IMF_Type, PC.AG, PC.IMF_ID,
ROW_NUMBER() OVER(
ORDER BY PC.Borrower_FName, PC.Borrower_LName, PC.DateClosed, PC.DebtID, PC.IMF_Type, PC.AG, PC.IMF_ID) AS RowNumber
FROM v_MyIMFs PC) v_MyIMFs
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
AND IMF_ID IN (51,52)
ORDER BY Borrower_FName,
Borrower_LName,
DateClosed,
DebtID,
IMF_Type,
AG,
IMF_ID
GO

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-28 : 06:58:09

evan it looks like your use of the row_number function itself is fine, but you are filtering out the rows AFTER you generate the row numbers. You should filter and then generate the row numbers as in:

USE IMF_ED
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 1
SET @EndRow = 50

SELECT Borrower_FName,
Borrower_LName,
DateClosed,
DebtID,
IMF_Type,
AG,
IMF_ID
FROM (
SELECT PC.Borrower_FName, PC.Borrower_LName, PC.DateClosed, PC.DebtID, PC.IMF_Type, PC.AG, PC.IMF_ID,
ROW_NUMBER() OVER(
ORDER BY PC.Borrower_FName, PC.Borrower_LName, PC.DateClosed, PC.DebtID, PC.IMF_Type, PC.AG, PC.IMF_ID) AS RowNumber
FROM v_MyIMFs PC where IMF_ID IN (51,52)) v_MyIMFs
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
AND IMF_ID IN (51,52)
ORDER BY Borrower_FName,
Borrower_LName,
DateClosed,
DebtID,
IMF_Type,
AG,
IMF_ID
GO

evanburen
Posting Yak Master

167 Posts

Posted - 2011-03-28 : 13:08:35

That works great. Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources