Paging And Sorting With RowNum()

I apologize in advance, but this post might get somewhat lengthy.

I'm new to the whole pagiong and sorting in SQL Server 2005, and I'm trying to get my SQL to perform in a certain way but can't seem to nail it just down. Hopefully someone can provide some insight or direction. Here's the scoop:

The gui sorts on any column chosen. For example, there's USER, ADDRESS, CITY, STATE, ZIP. The gui allows you to choose how many rows you wish to display per page. If there are 500 rows that meet the search criteria and you choose five pages, there should be 100 records per page. Here's the code:

INSERT INTO #RESULTS

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description

, AC.AccountProvider

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentId,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentId = PY.AccountPaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize

AND ((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%'))

AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId))

AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId))

AND ((@PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time

AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber))

AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled))

AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued))

AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid))

AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus))

AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus))

ORDER BY AccountPaymentID

--GET A COUNT OF THE ROWS SELECTED

SELECT @TotalRows = Count(*)

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.PaymentId,

ROW_NUMBER() OVER(ORDER BY PY.PaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.PaymentId = PY.PaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE

((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%'))

AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId))

AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId))

AND ((@PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time

AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber))

AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled))

AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued))

AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid))

AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus))

AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus))

SET @ORDERBY = ' ORDER BY ' + @SORT --END

--CASE WHEN @Sort IS NULL THEN '' ELSE

EXEC('SELECT * FROM #RESULTS ' + @ORDERBY)

--SET @TOPSQL = 'SELECT TOP ' + Convert(VarChar,@PageSize) + '* FROM #RESULTS ' + @ORDERBY

--SELECT @TOPSQL

--EXEC (@TOPSQL)

SET @PAGES = Round(@totalRows / @PageSize,0,1) + CASE WHEN @TotalRows % @PageSize = 0 THEN 0 ELSE 1 END

-- Return Total number of pages and Total number of Rows

SELECT @PAGES AS PageCount,

@TOTALROWS AS TotalRecords

I get back the rows I'm expecting and it looks fine. What I can't get to happen is the proper sort.



I get back records 1014 records - 1001 through 2014. I choose to display 400 records per page, so ther will be 3 pages total (1001 through 1400 on page 1, 1401 through 1800 on page 2, and 1801 through 2014 on page 3. All the records are sorted by RECORD NUMBER (1000, 1001, etc.)



What I would like to do is when I choose to sort on the column (ASC or DESC),

1.) The entire record set is esssentially retrieved again,

2.) The record set is resorted in the proper order

3.) The record set is redisplayed.

For example, if I'm on Page 2, and I choose to sort in DESCending order, Page 1 would then have records 2014 through 1615, Page 2 would display 1614 through 1215, and page 3 would have 1214 through 1001. Since I was already on Page 2, I would be seeing Page 2 with the new sort. Now when I resort , it just sorts the records on the individual pages, not the entire result set.

Hopefully this all made sense...!

If anyone has any advice or insight, please don't hesitate!

Thank You!!