Limit and Offset for web paging in SQL Server

Question

Limit and Offset for web paging in SQL Server

Navigation

#1 by (7 votes)

8

I'm working with PHP and I have about 8000 records currently in DB.

As it is for a web page, I need to make a pagination to be viable reading the data.

As I understand it, I have to do something similar to limit and offset to show 50 out of 50 pages, but I do not know how to do this with SQL Server.

IneedtoworkwithJoinbelow:

selectos.id,os.dataHora,material.nomeasnomeEquip,cliente.nomeFantasia,setor.nomeasNomeSetor,os.motivoOs,(selectsum(itemMaterial.valorUnitario)fromosasOS1innerjoinitemMaterialonitemMaterial.id=os.idItemMaterialwhereOS1.id=os.id)asTotalMaterial,tipoOs.nomeasNomeTipoOS,itemMaterial.nSerie,itemMaterial.rm,os.statusfromosinnerjoinitemMaterialonitemMaterial.id=os.idItemMaterialinnerjoinmodeloonmodelo.id=itemMaterial.idModeloinnerjoinmaterialonmaterial.id=itemMaterial.idMaterialinnerjoinclienteoncliente.id=os.idClienteinnerjoinsetoronsetor.id=os.idSetorinnerjoinusuarioonusuario.id=os.idUsuarioSolicitanteinnerjointipoOsontipoOs.id=os.idTipoOswherecliente.id=(selectusuario.idClientefromusuariowhereusuario.login='julio')orderbydataHoradesc

GIST

How to proceed in this case?

sql-server query select

asked by anonymous 28.11.2016 / 13:35

1 answer

FluentNHibernate.Cfg.FluentConfigurationException.GetObjectData how to solve? What is the best way to use projection histograms as features in classifiers or NNs without using a resize in the image

score 7 · Accepted Answer

Starting with SQL 2012

SELECT coluna1,
       ...
  FROM tabela
 WHERE ...
 ORDER BY coluna1,
          ...
 OFFSET @Offset ROWS
 FETCH NEXT @Limit ROWS ONLY;

In your case you can change the variable @Limit to @QtdPorPagina and @Offset to (@Pagina - 1) * @QtdPorPagina and use as follows:

DECLARE @QtdPorPagina INT = 50,
        @Pagina       INT = 1;

Replacing in your query

DECLARE @QtdPorPagina INT = 50,
        @Pagina       INT = 1;

SELECT os.id,
       os.dataHora,
       material.nome AS nomeEquip,
       cliente.nomeFantasia,
       setor.nome AS NomeSetor,
       os.motivoOs,
       (SELECT SUM(itemMaterial.valorUnitario)
          FROM os AS OS1
               INNER JOIN itemMaterial ON itemMaterial.id = os.idItemMaterial
         WHERE OS1.id = os.id ) AS TotalMaterial,
       tipoOs.nome AS NomeTipoOS,
       itemMaterial.nSerie,
       itemMaterial.rm,
       os.status
  FROM os
       INNER JOIN itemMaterial ON itemMaterial.id = os.idItemMaterial
       INNER JOIN modelo ON modelo.id = itemMaterial.idModelo
       INNER JOIN material ON material.id = itemMaterial.idMaterial
       INNER JOIN cliente ON cliente.id = os.idCliente
       INNER JOIN setor ON setor.id = os.idSetor
       INNER JOIN usuario ON usuario.id = os.idUsuarioSolicitante
       INNER JOIN tipoOs ON tipoOs.id = os.idTipoOs
 WHERE cliente.id = (SELECT usuario.idCliente
                       FROM usuario
                      WHERE usuario.login = 'julio')
 ORDER BY dataHora DESC
OFFSET (@Pagina - 1) * @QtdPorPagina ROWS
 FETCH NEXT @QtdPorPagina ROWS ONLY;

For SQL Server prior to 2012

For a similar result you can use the ROW_NUMBER() function to generate the line number and work with this information as follows:

WITH resultado AS
(SELECT coluna1,
        ...,
        ROW_NUMBER() OVER (ORDER BY coluna1, ...) AS linha
   FROM tabela
  WHERE ...)
SELECT *
  FROM resultado
 WHERE linha >= @Offset
   AND linha < @Offset + @Limit

Similar question from SOen : Equivalent of LIMIT and OFFSET for SQL Server?