CODEWITHSUNDEEP
sqlsql-serverpagination
Raccoon
Raccoon

Reputation: 1427

SQL Server paging query

Urggggg! I've been struggling with this for a long time! I can do it with MySQL so easy but not with SQL Server :(

Here are the simplified tables which should be joined all together.

enter image description here

Combining all of them by using inner join syntax, I have to write a query to use for paging in the future (btw, PHP). Let's say I need all ID, Name, and Date info which a picture is taken between 2012-10-01 and 2012-10-30.... and 20 people per page.

What would be the easiest query to achieve the goal here? (I tried NOT IN.. but it was so buggy cuz I'm not used to 'NOT IN' stuff...)

You can ignore the performance speed.

Thank you!

Upvotes: 3

Views: 22950

Answers (9)

Hashim KM
Hashim KM

Reputation: 11

We can achieve the same by CTE (Common Table Expressions).First we need to set current page number and offset of the result from which we need to fetch.Then we have to order the result set by ROW_NUMBER.and store the result by using cte feature.then filter the result with page size and offset against Row number. The SQL query is as follows

DECLARE @PageSize INT=1 ,@PageNumber INT=2

DECLARE @Offset int =(@PageSize * (@PageNumber - 1))+1

;WITH Results_CTE AS
(

    SELECT *,
                   ROW_NUMBER() OVER (ORDER BY FieldName) AS RowNum
    FROM TABLENAME
  )  

  SELECT *
  FROM Results_CTE
  WHERE RowNum>=@Offset AND RowNum < @Offset + @PageSize

END

https://amonghorizon.blogspot.com/2020/07/sql-server-query-for-pagination-control.html

Upvotes: 1

Peyman Mehrabani
Peyman Mehrabani

Reputation: 729

Microsoft added native paging features in SQL Server 2012 and above using "OFFSET" and "FETCH". You can use this feature as below:

-- Skip the first 500 rows and return the next 100
SELECT *
FROM TableName
ORDER BY [ID]
    OFFSET 500 ROWS
    FETCH NEXT 100 ROWS ONLY;

For the OFFSET __ and FETCH NEXT __ clauses, you can specify constant values (as above), or you can specify variables, expressions, or constant scalar subqueries.

Upvotes: 3

howard
howard

Reputation: 29

I liked Taha Siddiqui's answer except that it requires modifying the passed in query and it didn't work for an SQL UNION statement that I have to use due to some very poor design decisions made by a former co-worker.

The generic SQL Server query is:

SELECT * FROM (
    select ROW_NUMBER() OVER (order by ID) as row_num, * FROM (
      <<Put Your Query Here>>
    ) AS tempTable1
) AS tempTable2 WHERE row_num >= ((pageNum -1) * pageSize) AND row_num < ((pageNum -1) * pageSize) + pageSize;

I created a Java function that assumes one-based paging:

    public static String buildPagingQuery(String sqlStr, String sortExpression, int pageNum, int pageSize) {
        if (StringUtils.isBlank(sortExpression)) { //NOTE: uses org.apache.commons.lang.StringUtils
            sortExpression = " (select 0)";
        }

        int startIndex = ((pageNum - 1) * pageSize) + 1;
        int endIndex = startIndex + pageSize;

        StringBuilder sb = new StringBuilder();

        sb.append("SELECT * FROM (");
        sb.append("SELECT ROW_NUMBER() OVER (ORDER BY ");
        sb.append(sortExpression);
        sb.append(") as row_num, * FROM (");
        sb.append(sqlStr);
        sb.append(") as tempTable1 ");
        sb.append(") AS tempTable2  "); 
        sb.append("WHERE row_num >= ").append(startIndex);
        sb.append(" AND row_num < ").append(endIndex);

        return sb.toString();
    }

I have not yet checked performance on a large table.

Upvotes: 1

Taha Rehman Siddiqui
Taha Rehman Siddiqui

Reputation: 2533

You can use the methods from the following class

internal class PagingHelper
{
    public static String ParseQueryForPagingAndSorting(String strSQL, string SortExpression, int StartIndex, int EndIndex)
    {
        if (String.IsNullOrEmpty(SortExpression))
            SortExpression = " (select 0)";
        StringBuilder sb = new StringBuilder();
        sb.Append("select * from (");
        sb.Append(" select ROW_NUMBER() OVER (ORDER BY " + SortExpression + ") AS row_num,");
        int index = strSQL.ToLower().IndexOf('t', 0);
        sb.Append(strSQL.Substring(index + 2));
        sb.Append(")");
        sb.Append(" AS TempTable");
        sb.Append(" where row_num>=" + StartIndex.ToString() + " AND row_num<=" + EndIndex.ToString());
        return sb.ToString();
    }
    public static String ParseQueryForCount(String strSQL)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append("select count(*) from");
        sb.Append("(");
        sb.Append(strSQL);
        sb.Append(")");
        sb.Append(" AS TempTable");
        return sb.ToString();
    }
}

Upvotes: 0

Mike Monteiro
Mike Monteiro

Reputation: 1457

This is how I would do it in SQL Server 2005+:

SELECT ID, Name, Photo, CreatedDate, rowNum, (rowNum / 20) + 1 as pageNum
FROM (
    SELECT a.ID, a.Name, b.Photo, c.Created_Date
       , Row_Number() OVER (ORDER BY c.Created_Date ASC) as rowNum
    FROM a
       JOIN b ON a.ID = b.ID
       JOIN c ON c.photo = b.photo
    WHERE c.Created_Date BETWEEN '2012-01-1' AND '2012-10-30'
) x
WHERE (rowNum / 20) + 1 = 1

Note that I'm using a little integer division trickery to calculate page number.

Since pre-2005 sadly doesn't have row_number(), I'd use an intermediate table with an identity column:

    SELECT a.ID, a.Name, b.Photo, c.Created_Date
       , identity(int,1,1) as rowNum
    INTO t
    FROM a
       JOIN b ON a.ID = b.ID
       JOIN c ON c.photo = b.photo
    WHERE c.Created_Date BETWEEN '2012-01-1' AND '2012-10-30'
    ORDER BY c.Created_Date ASC
    GO

    ALTER TABLE t ADD pageNum AS rowNum / 20
    GO

    SELECT ID, Name, Photo, Created_Date, rowNum
    FROM t
    WHERE pageNum = 1

Upvotes: 6

Bill Rawlinson
Bill Rawlinson

Reputation: 600

Here is how I did it a long time ago..

    SELECT * FROM (
      SELECT TOP y * FROM (
           SELECT TOP x * FROM sometable
           ORDER BY somefield ASC
      )
      ORDER BY somefield DESC)
ORDER BY somefield

The innermost query, SELECT TOP x, grabs the first x number of rows, the second query SELECT TOP y, gets the last y of x rows, and the outermost query, SELECT * puts the results in the right order.

Here is a blog post that explains how it works Here is a blog article I wrote back in 2006 talking about it http://code.rawlinson.us/2006/12/t-sql-query-paging.html

The tl;dr; of the post is from this paragraph:

For example, let’s say we want the first page, so the top 20 results. That is pretty easy, just use SELECT TOP 20 …. but what about the second or subsequent pages? How do you get the 21-40 items? It’s easier than you might suspect. What you are actually trying to get is the bottom y of the top x results. To look at that another way you want the top y of the top x results ordered backwards.

It's up to you how you calculate and provide the x and y values to the query.

Upvotes: 4

wwanich
wwanich

Reputation: 61

Try this with Sql Server 2008 + AdventureWorks database

DECLARE @PageIndex INT, @RowsPerPage INT
DECLARE @StartRow INT, @EndRow INT;

SET @PageIndex = 1;
SET @RowsPerPage = 500;
SET @StartRow = ((@PageIndex - 1) * @RowsPerPage) + 1;
SET @EndRow = @StartRow + @RowsPerPage - 1;

--append#1
WITH PAGE_ROWS
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate DESC, SalesOrderNumber ASC) AS ROW_NO
    , COUNT(*) OVER() AS TOTAL_ROWS
    , *
FROM( 
    --working query
    SELECT S.SalesOrderID
        , S.SalesOrderNumber
        , S.OrderDate
        , S.DueDate
        , S.ShipDate
        , S.Status
        , S.PurchaseOrderNumber
        , C.AccountNumber
        , P.FirstName, P.MiddleName, P.LastName
    FROM [Sales].[SalesOrderHeader] AS S
        LEFT OUTER JOIN [Sales].[Customer] AS C ON C.CustomerID=S.CustomerID
        LEFT OUTER JOIN [Person].[Person] AS P ON P.BusinessEntityID=C.PersonID
--append#2
) AS Src)
SELECT CEILING(TOTAL_ROWS/ CAST(@RowsPerPage AS DECIMAL(20,2))) AS TOTAL_PAGES
    ,*
FROM PAGE_ROWS
WHERE ROW_NO BETWEEN @StartRow AND @EndRow
ORDER BY OrderDate DESC, SalesOrderNumber ASC

Upvotes: 2

Muhammad ADIL
Muhammad ADIL

Reputation: 303

ALTER  Proc [dbo].[Sp_PagingonTable] 
@SearchText varchar(255) = null,
@ChannelName varchar(255)= null,
@ChannelCategory varchar(255)= null,
@ChannelType varchar(255)= null,
@PageNo int,
@PageSize int,
@TotalPages int output
As
DECLARE  @VariableTable TABLE
(
Rownumber INT,
ReferralHierarchyKey BIGINT, 
ReferralID VARCHAR(255), 
ChannelDetail VARCHAR(255),     
ChannelName VARCHAR(255),
ChannelCategory VARCHAR(255),
ChannelType VARCHAR(255)
)
 /*---Inserting Data into variable Table-------------*/
INSERT INTO @VariableTable
SELECT   
 ROW_NUMBER() OVER(ORDER BY [ColumnID) as Rownumber,*
 FROM [dbo].[TableName]
 WHERE (@SearchText IS NULL OR ChannelDetail LIKE '%' + @SearchText + '%')                                 And       (@ChannelName  IS NULL OR ChannelName  = @ChannelName )
/*--Applying Paging on filter Table--*/
SELECT 
        ReferralID
       ,ChannelDetail
       ,ChannelName
       ,ChannelCategory
       ,ChannelType 
    FROM 
        @VariableTable
WHERE Rownumber between (((@PageNo -1) *@PageSize)+1) and @PageNo *    @PageSize
 /*--Getting Total Pages After filter Table---*/
 SELECT @TotalPages =  (Count(*) + @PageSize - 1)/@PageSize FROM  @VariableTable
 SELECT @TotalPages

Upvotes: 1

Systematix Infotech
Systematix Infotech

Reputation: 2365

There is a another way I have found to do this into Sql server 2012

http://raresql.com/2012/07/01/sql-paging-in-sql-server-2012-using-order-by-offset-and-fetch-next/

Upvotes: -1

Related Questions