Paging And Sorting With RowNum()

Aug 13, 2007

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

View 2 Replies


ADVERTISEMENT

Paging And Sorting Using ROWNUM()

Aug 14, 2007

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 Snippet
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!!

View 11 Replies View Related

HELP: Paging And Sorting

Apr 13, 2008

Hi guys



I know this topic has been gone overed a bit but it just seems that no one has a really good answer.



What i need it to be able to be able to pass in which index row i want to go from and to, as weel a a token which corresponds to how it should be sorted.



The problem with the methods that i have seen to do this is that they all use a case statement to handle the sorting like the below;






Code Snippet


;WITH TotalSales AS (

SELECT CASE @OrderBy

WHEN 'UnitPrice' THEN ROW_NUMBER() OVER (ORDER BY UnitPrice)

WHEN 'OrderQty' THEN ROW_NUMBER() OVER (ORDER BY OrderQty)

WHEN 'CarrierTrackingNumber' THEN (ROW_NUMBER() OVER (ORDER BY CarrierTrackingNumber))

END AS RowNumber,

CarrierTrackingNumber,

UnitPrice,

OrderQty

FROM Sales.SalesOrderDetail

WHERE CarrierTrackingNumber LIKE '%F%'

)



SELECT *

FROM TotalSales

WHERE RowNumber between @StartIndex and @StartIndex + 9




At the begging this looks really good but it turns out that this is really slow. In fact it is about twice as slow as using the below dynamic SQL:




Code Snippet


SET @SafeOrderBy = CASE @OrderBy

WHEN 'UnitPrice' THEN 'UnitPrice'

WHEN 'OrderQty' THEN 'OrderQty'

WHEN 'CarrierTrackingNumber' THEN 'CarrierTrackingNumber'

END


SET @temp = N'

SELECT *

FROM (

SELECT ROW_NUMBER() OVER (ORDER BY ' + @SafeOrderBy + ') AS RowNumber,

CarrierTrackingNumber,

UnitPrice,

OrderQty

FROM Sales.SalesOrderDetail

WHERE CarrierTrackingNumber LIKE ''%F%''

) SUB

WHERE SUB.RowNumber between ' + @StartIndexAlt + ' and ' + @StartIndexAlt + ' + 9'

EXEC sp_executesql @temp





Now for a whole heap of reasons I would like to avoid using dynamic SQL to do this but if the alternative means that my queries take twice as long i dont see i have much of a choice.

Does anyone have any ideas??
Thanks
Anthony

View 6 Replies View Related

Paging And Sorting

Jan 20, 2008

Hi guys

I am wondering if anyone has any practical ways of sorting and paging within SQL server 2005. I have seen plenty of different example and there seems to numerous ways of doing it but i was wondering if anyone has a method that they have been using that they know works in enterprise level solutions and will work in the majority of cases as a standard. The method that has stood out thus far is Common table expressions but I am not sure if this is the best or optimal approach and whether it will work in with dynamic sorting.
Thanks

Anthony

View 6 Replies View Related

Sorting && Paging With Recursive Queries

Jun 14, 2007

Hi, can anyone tell me how I can get implement sorting and paging using a recursive query?
I have a created a stored procedure (bit like the simple example below), but would like to add in sorting and paging using order by and row_number().
DECLARE @CategoryID intSET @CategoryID = 12;WITH CTE_Example (CategoryID, CategoryName, ParentID, Depth, RowNum)AS(SELECT CategoryID, CategoryName, ParentID, 0 AS Depth, Row_Number() OVER (ORDER BY CategoryName) AS RowNumFROM Categories WHERE CategoryID = @CategoryIDUNION ALLSELECT Categories.CategoryID, Categories.CategoryName, Categories.ParentID, CTE_Example.Depth + 1 AS Depth, Row_Number() OVER (ORDER BY Categories.CategoryName) AS RowNumFROM CategoriesJOIN CTE_Example ON Categories.ParentID = CTE_Example.CategoryID)SELECT * FROM CTE_Example WHERE RowNum BETWEEN @Start AND @End ORDER BY RowNum
I think the problem comes down to the Union, appreciate if someone can help.
Many thanks,
Matt

View 1 Replies View Related

Sorting + Paging A Large Table In Stored Procedure

May 6, 2007

As I said above, how do I put sorting + paging in a stored procedure.My database has approximately 50000 records, and obviously I can't SELECT all of them and let GridView / DataView do the work, right? Or else it would use to much resources per one request.So I intend to use sorting + paging at the database level. It's going to be either hardcode SQL or stored procedures.If it's hardcode SQL, I can just change SQL statement each time the parameters (startRecord, maxRecords, sortColumns) change.But I don't know what to do in stored procedure to get the same result. I know how to implement paging in stored procedure (ROW_NUMBER) but I don't know how to change ORDER BY clause at runtime in the stored procedure.Thanks in advance.PS. In case "ask_Scotty", who replied in my previous post,   http://forums.asp.net/thread/1696818.aspx, is reading this, please look at my reply on your answer in the last post. Thank you.

View 3 Replies View Related

Sorting On A Temporary Field With A Custom Paging Method

May 18, 2005

I've made another topic before concerning this problem, but since  it was really confusing, I will made one clearer (it was about orthodromic formula, in case you read it, but the problem change during the topic, so thats why im creating this new one too).
I have a stored procedure with custom pagin method inside, and I want to sort my records on a fields I create myself  (which will receive a different value for each record.)  Now, I want to sort on this temporary field.  And since this is a custom paging method I can choose between many page.  Now, for the first page, it sorts fine.  But when I choose a page above the first one, the sorting is not right (the results all are wrong). 
So my real question is: is it really possible to sort on a Temporary Field in a custom paging method (because I know I can do it without any problem on a real field from my table, it just doesnt work right when I try on a temporary field).  I tried to solve my problem with this little SQL instruction, but it didnt give me any result yet:
SELECT TOP 20 PK,  test = field_value FROM Table WHERE PK not in (SELECT TOP 10 ad_id FROM Table ORDER BY ?) ORDER BY ?
well thanks for taking the time to read this, any help woulb be appreciated.

View 17 Replies View Related

Evaluate My Stored Procedure For Paging, Filtering, And Sorting

Apr 28, 2008

This is for SQL Server 2000. The purpose of the procedure is to return a subset of a filtered and sorted result set. The subset, filter criteria, and sort column and sort direction can be set dynamically. It uses the rowcount technique for paging.

This would be used to drive an ASP.NET gridview which supports filtering of the data, paging, and sorting. Please let me know what improvements I can make or if you have an idea for a better solution. (I didn't put this in a vBulletin code block because personally I find two sets of scroll bars annoying, but I can if people think it's better).

CREATE PROCEDURE dbo.Books_GetFilteredSortedSubset
(
-- paging
@startRowIndex INT = 1,
@maximumRows INT = 999999,

-- sorting
@sortColumn NVARCHAR(30) = 'title_id',
@sortDirection NVARCHAR(4) = 'ASC',

-- filtering
@title VARCHAR(100) = NULL,
@type VARCHAR(30) = NULL,
@price MONEY = NULL
)
AS
BEGIN

DECLARE @sql NVARCHAR(4000)
DECLARE @parameters NVARCHAR(4000)
DECLARE @tableSource NVARCHAR(4000)
DECLARE @orderByExpression NVARCHAR(4000)
DECLARE @searchCondition NVARCHAR(4000)
DECLARE @uniqueKey NVARCHAR(30)


-- set the unique key used to ensure the rows are sorted deterministically
SET @uniqueKey = 'title_id'



-- build the FROM table source used throughout this procedure
SET @tableSource = 'titles t
inner join publishers p on t.pub_id = p.pub_id'




-- build the WHERE search condition used to control filtering throughout this procedure

SET @searchCondition = '(1 = 1)'

IF @title IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (title LIKE ''%' + @title + '%'')'
IF @type IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (type LIKE ''' + @type + '%'')'
IF @price IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (price = ' + CAST(@price AS NVARCHAR) + ')'




-- build the ORDER BY expression used to control the sorting throughout this procedure

SET @orderByExpression = @sortColumn + ' ' + @sortDirection

-- add uniqeKey to ORDER BY statement to ensure consistent ordering of results when @sortColumn is not unique
IF @sortColumn <> @uniqueKey
SET @orderByExpression = @orderByExpression + ', ' + @uniqueKey + ' ' + @sortDirection





-- Get the column value at the position specified by @startRowIndex when the results are sorted in the desired sort order

SET @sql = 'SET ROWCOUNT @rowcount; SELECT @start_row = ' + @sortColumn + ', @start_row_id = ' + @uniqueKey +
' FROM ' + @tableSource +
' WHERE ' + @searchCondition + ' ORDER BY ' + @orderByExpression

PRINT @sql

SET @parameters = '@rowcount INT, @start_row sql_variant OUTPUT, @start_row_id sql_variant OUTPUT'

DECLARE @start_row sql_variant
DECLARE @start_row_id sql_variant

EXEC sp_executesql @sql, @parameters, @rowcount = @startRowIndex, @start_row = @start_row OUTPUT, @start_row_id = @start_row_id OUTPUT



-- Get the filtered subset of results

-- add sql to filter the results based on criteria passed in as parameters
SET @sql = 'SET ROWCOUNT @rowcount; ' +
'SELECT
t.title_id,
t.title,
t.price,
t.type,
p.pub_name,
p.city,
p.state,
p.country
FROM ' + @tableSource +
' WHERE (' + @searchCondition + ') AND '

-- add sql to control the starting row
IF @sortDirection = 'ASC'
SET @sql = @sql + '( (' + @sortColumn + ' > @start_row) OR (' +
@sortColumn + ' = @start_row AND ' + @uniqueKey + ' >= @start_row_id) )'
ELSE
SET @sql = @sql + '( (' + @sortColumn + ' < @start_row) OR (' +
@sortColumn + ' = @start_row AND ' + @uniqueKey + ' <= @start_row_id) )'

-- add sql to control the ordering of everything
SET @sql = @sql + ' ORDER BY ' + @orderByExpression

PRINT @sql

SET @parameters = '@rowcount INT, @start_row sql_variant, @start_row_id sql_variant'

EXEC sp_executesql @sql, @parameters, @rowcount = @maximumRows, @start_row = @start_row, @start_row_id = @start_row_id


-- Reset the rowcount for others
SET ROWCOUNT 0

END;
GO

View 14 Replies View Related

(SOS!) Logic Paging Use Group, But Meet Sorting Problem

Jan 16, 2008

User's requirement:
use the SP get the dataset from DB at once.
Want to make an accurate count of paging ( 200 rows /page) at the SSRS side.
Need to provide sorting, user just need to click the according column header's caption.

The design is:
we add group to devide the data into 200 per unit. Choice 'page break at end'.
add 2 Report Parameters, SD & SF, means sorting direction and Field.
In the Table Parameters add:

=IIF(Parameters!SD.Value="Ascending",Fields(Parameters!SF.Value).Value,"")

=IIF(Parameters!SD.Value="Descending",Fields(Parameters!SF.Value).Value,"")

everything seems OK at this time, and the rpt is very quick.

The Bug is:
Test team found out the sorting was broken by group, because we Choice 'page break at end'.
Noe the sorting scope is just the first page (first group)

Help wants:
query DB once , Accurate paging, full scope sorting.




View 5 Replies View Related

Sorting And Grouping Question By Allowing Users To Select The Sorting Field

Feb 11, 2007

I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.

So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.



Any suggestions would help.


Thanks

View 1 Replies View Related

Reporting Services :: Horizontal Axis Show Last Value In First And Last Space When Sorting A-z But Shows Correctly When Sorting Z-a

Jul 10, 2015

SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int.  When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end.  The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order.  The sorting is implemented using the Category Group sorting option.

View 6 Replies View Related

Rownum

Nov 10, 2004

Hi folks,
SELECT * FROM mytable
100 rows returned.
Can i get a rownum column for each record; i.e. if 100 records returned; rownum order 1,2,3.....100 along with the each record position.
is it possible without using cursor?


Howdy!

View 4 Replies View Related

ROWNUM Function

Dec 1, 2005

Does SQL Server 2005 or SQL  Express have the capability of the ROWNUM function found in Oracle (LIMIT in MySQL)?please advice!To select records from row #10 to row #20Oracle: SELECT * FROM MyTable WHERE ROWNUM>9 AND ROWNUM<21MySQL: SELECT * FROM MyTable LIMIT 10,20SQL Server: ?

View 3 Replies View Related

Rownum Equivalent ?

Aug 16, 2004

Hi,

Rownum returns the serial number for the records in Oracle.
Id there an equivalent for the same in SQL Server ?

select rownum from test_table;

Please advise,

Thanks
Sam

View 1 Replies View Related

Rownum Alternate In MS-SQL

Jul 17, 2007

I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.

I want to get 100 rows onward to one particular data ... how can i ?

View 9 Replies View Related

Sql Server Rownum

Nov 13, 2007

hai, for one of my batch requirement i would like to retrieve records set by set, meaning if there are 1000 records which satisfies the condtion, i want to take first 100 record process it, and then again go to database retireve another 100 record and so on...
how can i do this...do i need use the for loop logic or, is there any simple way to achieve this? help pls

View 3 Replies View Related

Max(), Top N() Or Rownum() Function

Mar 19, 2008



Background: SQL Server 2000 transactional database

Key fact 1: I have one main fact table and several dimension tables. for each join between a dimension table and the fact table, I have a combined key that includes at least three fields one of which would be a date field.
Key fact 2: Sometimes fields in the dimension tables changes names over time e.g. a department that was called dept_1 a few years ago might have changed its name since and is now called dept_A today. The historic report should still use today's name of the department.

For example using three tables (keys are colour coded)
DEPT table and JOB table and Employee table

DEPT_table JOB_table EMPLOYEE_table
DEPTID DEPTID EMPLID
SETID DEPT_SETID EMPL_RCD
EFFDT EFFDT
DESCR_LONG EFFSEQ
DESCR_SHORT EMPLID
EMPL_RCD

Since departments change names over time, I intend to return just the most current department name for use in my reports but one way or another SOME records return more than one. I am using max(effdt) to extract the most recent from two tables like the ones above.












Code Snippet
DEPT_TBL.DEPTID=BO_JOB_VW.DEPTID and DEPT_TBL.SETID=BO_JOB_VW.SETID_DEPT and
DEPT_TBL.EFFDT=(select max(j.EFFDT) from DEPT_TBL j where
j.EFFDT<=BO_JOB_VW.EFFDT and
j.DEPTID=BO_JOB_VW.DEPTID and
j.SETID=BO_JOB_VW.SETID_DEPT)


and




Code Snippet
BO_JOB_VW.EMPLID = EMPLOYEE.EMPLID
BO_JOB_VW.EMPL_RCD = EMPLOYEE.EMPL_RCD





Although it is possible for an employee to belong to more than one department simultaneously, what I have notice is that for some employees that get multiple rows of departments, they have never belonged to more than one department or ever changed departments at all.

e.g



Code Snippet
DEPT
/*
DEPTID SETID EFFDT DESCR
DP1 GBR 01/01/1901
DP1a GBR 01/01/1965
DP2 GBR 01/01/1996
DP2a GBR 01/01/1998
DP2b GBR 01/01/2006
*/
DP1 and DP1a refers to the same department but reflect a change of names
DP2, DP2a and DP2b refer to one department but also reflect changes in the name

JOB
/*
DEPTID SETID EFFDT EMPLID EMPL_RCD EFFSEQ STD_HRS
DP1 GBR 01/01/1901 001 0 0 37.0
DP1a GBR 01/01/1999 002 0 0 37.5
DP2 GBR 01/01/1996 003 0 0 18.75
DP2a GBR 01/01/1998 003 0 1 22.2
DP2b GBR 01/01/2006 002 0 3 37.0
*/

EMPLOYEE
/*
EMPLID EMPL_RCD
001 0
002 0
003 0
003 0
002 0
*/

THe report should therefore look something like

EMPLID DEPTID STD_HRS EFFDT
001 DP1 37.0 01/01/1901
002 DP2b 37.5 01/01/1999
002 DP2b 37.0 01/01/2006
002 DP2a 18.75 01/01/1996
002 DP2a 22.2 01/01/1998




If you need any further details please let me know
code suggestions would be welcome and greatly appreciated
thanks

View 3 Replies View Related

Rownum Rowid

Jan 23, 2008



Hi. Is there a rownum, rowid, or autonumber in t-sql for SQL Server 2005?

Thanks!

View 10 Replies View Related

Is There Any Property Like Rownum In Oracle

Jul 30, 2004

Hi All....

I have to write one query where i have to display the ID as 1,2 3, 4 in oracle we used to ROWNUM to display ....in MS SQL server is there any property to show???

i have to display like

ID ProgramCount
1 4
2 6
3 5
4 1
5 2

Ashutosh

View 1 Replies View Related

Rowid,Rownum In SQL Server...

Oct 13, 2001

hello,
Do we have Rowid or Rownum in SQL Server, or any alternative.


Thanks,
venkat.

View 1 Replies View Related

What Is Rownum In SQLSERVER 7 As In Oracle

Nov 11, 2001

Hello,
How can this query be written in SQLSERVER 7 as it is written in Oracle.

SELECT rownum, column1 from XYZ;

This table returns two columns, first being the auto-generated sequence and second has the values in column1 of XYZ table.

I need to do similar in SQLserver 7 and I am new to it.
Please treat it as urgent..

Thanx

View 2 Replies View Related

Generalising Rownum For All The Databases

May 17, 2004

Hi

I have as issue with this rownum.This is supported in different forms in different databases like top in mssql ,limit in mysql and all..i want to genaralise this independent of the type of database because i want to run the query as i wont be knowing the type of the DB used by my user.

can anyone suggest a solution for this ? is there anyway of generating a series of numbers with the query by calling the function in that query repetitively ???


Regards
Sreenath

View 1 Replies View Related

Rownum And Rowid Equivalent In MS SQL

Mar 31, 2006

do we have any equivalent of rownum and rowid in MS SQL

View 7 Replies View Related

What Is The SQL Server Equivalent Of Oracle&#39;s ROWNUM

Jan 15, 2001

Is there an equivalent to Oracle's ROWNUM in SQL Server. ROWNUM, when added to a select statement as a column - the query would return an automatic counter, numbering each row returned.

View 1 Replies View Related

Is There Any Equivelant To Rownum Of Oracle In Sql Server

Jun 10, 2000

Hi all
I am new to sqlserve
Is there any equivelant to rownum of oracle.

if not then please can some body gudie me how to give unique
number to each row
like
1 xxx
2 yyy
3 zzz
4 aaa

Thanks pratik

View 2 Replies View Related

Oracle's ROWNUM Equivalent In SQL Server

Sep 2, 2004

Hi,

Can any one tell me is there anything in SQL Server thats equivalent
to Oracle's ROWNUM.

Note that the Identity Property or TOP n will not solve my problem.

I want to asign a sequence no. to each row when its being fetched.

For example if in the emp table there are 2000 rows and I write
the following query in Oracle ,

SELECT rownum , empno, empname FROM emp Where rownum < =3

I get the result like this

Rownum----Empno--------------Empname
------------------------------------------
1-----------2345---------------ABCD
2-----------3334---------------EFGH
3-----------4484---------------IJKL


I know I can limit the output rows in SQL Server by using TOP n. But
I also want to generate a sequence no. The identity property of SQL Server
will not be usefull here because my actaul WHERE clause will be more
complex like WHERE resigndate = '01-jan-2004'

Thanks

Asim Naveed

3

View 2 Replies View Related

RowNum Reset For Every Number Of Rows

Feb 15, 2015

How can I reset the number of Row_Number() if I exceed the number of rows specified.

Example:

Code:
Col1 RowNum
----------------------
ID1 1
ID1 2
ID1 3
ID1 4
ID1 5
ID1 1
ID1 2
ID1 3
ID1 4

In this example, the Row_Number will reset for every 5 items.

View 1 Replies View Related

Equivalent Oracle Rownum In SQLServer

Jul 23, 2005

Hello,I would like to know if the equivalent Oracle rownum exist inSQLServer. Here is a sample SQL code to explain what I want to do :selectjobs.name,jobs.job_id,jobs.description,hist.message,hist.step_name,hist.step_id,hist.run_status,hist.run_date,hist.run_time,hist.run_durationfrommsdb.dbo.sysjobs jobs,msdb.dbo.sysjobhistory histwherejobs.job_id=hist.job_idand hist.job_id='E71CCB97-81C3-46E2-83FA-BFFCB66B47F8'order byrun_date, run_timeI just want the first or second row returned by this query. In Oracle Ican simply add rownum=1 or rownum=2 in the where clause to obtain thedesired answer. I don't know how to do in SQLServer.Thank in advance,Pierig.

View 4 Replies View Related

There Is No Direct Equivalent To Oracle's Rownum Or Row Id In SQL Server

Sep 23, 2006

hi,

There is no direct equivalent to Oracle's rownum or row id in SQL Server

there is any possiblity are alternate thing is exist then please reply



regards

santosh

View 10 Replies View Related

Paging

Oct 12, 2004

Is there any way to implement a paging scheme such that only the required records are transferred from SQL Server to the asp.net app?

The only support I can find such as the DataAdaptor.Fill will bring all the records back from SQL Server and then create the page...

This obviously still takes time and memory based on the entire query, not the page size.

Any ideas?

View 2 Replies View Related

SQL Paging

Jul 26, 2005

I have a table with a lot of records.  I want to make paging without passing all the data back to a dataset.  I know how to select the top n rows but how do I select 10-20 for example. 

View 3 Replies View Related

T-SQL Paging

Dec 9, 2005

Hello,
How can I do paging in my SQL Server stored procedure.
I have thought of a way, but I need to :

"SELECT TOP @Count..."

which is not allowed :S

What can I do to get around this?

View 1 Replies View Related

Paging In Sql 2k

Dec 11, 2003

I have noticed that the server i'm running SQL2k on is starting to page out of the norm. I can see that the regsvc and sqlservr svc are showing high page faults/sec. I have 3 gigs of ram and set the max that sql can use to 2 gigs. It is currently using only 168 MB and still will show high paging at random times. I know I can add more ram but that doesn't seem to be the problem. I have also stopped unnecessary services at the os level.

Any other suggestions to fix this?

Thanks in advance.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved