March 28, 2009 at 5:55 pm
I have played a little bid with this code.
Here are 2 methods, 1. method that search a record and 2. method that retrives a page.
1. method
-- big thanks goes to Jacob Sebastian, creator of this algorithm
-- regards Davor Geci
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
--1. if you want to retrive page on whitch is the record @FindThisRecord
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
DECLARE @PageNumber AS BIGINT = 1;
DECLARE @Pages AS FLOAT = 0;
DECLARE @RowsPerPage AS FLOAT = 100;
DECLARE @RecIDNum AS BIGINT = 0;
DECLARE @Records AS FLOAT = 0;
DECLARE @FindThisRecord AS FLOAT = 98111220929667;
--**************************** PART 1 *****************************
-- order records by Order By
-- if you have WHERE or JOINS put also here for accurate results
-- here you need to retrive only RecID and field for searching your record
WITH tmp1 AS
(SELECT ROW_NUMBER () OVER (ORDER BY UFAGL.BRRAC DESC ) AS RecID,
UFAGL.BRRAC
FROM UFAGL)
-- get the position of my record and total number of records
SELECT @RecIDNum = RecId,
@Records = (select max(RecID) from tmp1)
FROM tmp1
WHERE BRRAC = @FindThisRecord -- if you don't have record to find skip this WHERE
SET @PageNumber = ceiling (@RecIDNum/@RowsPerPage) ; -- get the page with my record, if you don't have record to find skip this
SET @Pages = ceiling (@Records/@RowsPerPage) ; -- get number of total pages
--**************************** PART 2 *****************************
-- this is the acctual sql for getting my records ordered by order by field
-- if you have WHERE or JOINS put also here for accurate results
WITH tmp AS
(SELECT ROW_NUMBER () OVER (ORDER BY UFAGL.BRRAC DESC ) AS RecID ,
[UFAGL].[STATUS] AS [St] ,
[UFAGL].[SKLAD] AS [Sklad],
[UFAGL].[TIP] AS [Knj]
FROM UFAGL)
-- return only needed page of records from tmp
SELECT [St] ,
[Sklad],
[Knj]
FROM tmp
WHERE RecID BETWEEN
(((@PageNumber-1) * @RowsPerPage) + 1)
AND
(@PageNumber * @RowsPerPage)
--**************************** PART 3 *****************************
--Return values:
SELECT @Pages -- return number of pages
SELECT @PageNumber -- return actual page
SELECT @Records -- return number of total records
2. method:
--- big thanks goes to Jacob Sebastian, creator of this algorithm
--- regards Davor Geci
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
--2. if you only want to retrive specific page (@PageNumber)
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
DECLARE @PageNumber AS BIGINT = 2;
DECLARE @Pages AS FLOAT = 0;
DECLARE @RowsPerPage AS FLOAT = 100;
DECLARE @Records AS FLOAT = 0;
--**************************** PART 1 *****************************
-- if you have WHERE or JOINS put also here for accurate results
SELECT @Records = COUNT(*)
FROM UfaGl
SET @Pages = ceiling (@Records/@RowsPerPage) ; -- get number of total pages
--**************************** PART 2 *****************************
-- this is the acctual sql for getting my records ordered by order by field
-- if you have WHERE or JOINS put also here for accurate results
WITH tmp AS
(SELECT ROW_NUMBER () OVER (ORDER BY UFAGL.BRRAC DESC ) AS RecID ,
[UFAGL].[STATUS] AS [St] ,
[UFAGL].[SKLAD] AS [Sklad],
[UFAGL].[TIP] AS [Knj]
FROM UFAGL)
-- return only needed page of records from tmp
SELECT [St] ,
[Sklad],
[Knj]
FROM tmp
WHERE RecID BETWEEN
(((@PageNumber-1) * @RowsPerPage) + 1)
AND
(@PageNumber * @RowsPerPage)
--**************************** PART 3 *****************************
--Return values:
SELECT @Pages -- return number of pages
SELECT @PageNumber -- return actual page
SELECT @Records -- return number of total records
April 26, 2010 at 6:10 am
You should look at this solution: http://www.15seconds.com/Issue/070628.htm
It is a more generic version of what you are looking for. It does paging in terms of page number and # of records per page, supports sorting and filtering, and can be applied to virtually any table or view.
It also returns the total # of records being paged, as many posters were requesting.
Viewing 2 posts - 61 through 61 (of 61 total)
You must be logged in to reply to this topic. Login to reply