Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: 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:

    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!!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    I'm just a grunt query writer, so I'll post a method that the smarter folks here can upstage with the better solution. But I know this works since I had just that issue today.

    I'd write a View containing the basic query, then write a Stored Procedure that receives a Sort code. Then I would put a series of IF statements and order the results accordingly.

    Since you have a lot of columns; if the data isn't write/update intensive and the results are often large, consider indexing the view. I *think* sorting by an indexed column is faster, but I'm going to duck anyway for the responses that one will give.

    My thinking is that since it's all hard coded into the stored procedure it'll all get compiled just the once (as opposed to Dynamic SQL).

    So; something like this:
    Create Procedure sp_MyData (@SortCode int)

    if @SortCode = 1
    select * from qryMyView
    order by qryMyView.SortColumn1

    if @SortCode = 2
    select * from qryMyView
    order by qryMyView.SortColumn2

    GO
    Obviously; replace "Sort Code" with actual column names for clarity if you wish.
    Last edited by vich; 08-13-07 at 22:23. Reason: added comment

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    That's not gonna work...

    The reason there is dynamic SQL is because you never know what someone is going to be passing in...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •