How to return the total quantity when using OFFSET and FETCH NEXT?

Question

How to return the total quantity when using OFFSET and FETCH NEXT?

Navigation

#1 by (1 votes)

1

I needed to use OFFSET and FETCH NEXT to return a range of data between my query.

Table

CREATE TABLE [dbo].[Usuario]
(
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Email] [nvarchar](max) NULL,
  [Nome] [nvarchar](max) NULL,
  [SobreNome] [nvarchar](max) NULL
);

INSERT INTO [dbo].[Usuario]([Email],[Nome],[SobreNome])
VALUES
(1,'[email protected]','Marconi Barroso'),
(2,'[email protected]','Magno Barroso');

Select:

SELECT COUNT(1) Total, * from Usuario
GROUP BY Id, Email, Nome, SobreNome
ORDER BY Id
OFFSET 1 ROWS
FETCH NEXT 2 ROWS ONLY

But I need to know what the total amount of registration is because I'm paging the results in a table.

I'm not sure how to do this, but I'm not sure how to do this.

SqlFiddle

sql sql-server

asked by anonymous 27.04.2018 / 01:24

1 answer

How to group buttons inside a column in a table so that they do not change when the column is adjusted Add values to a deserialized JSON object attribute in Javascript

score 1 · Accepted Answer

Here are two alternatives. The first making use of the COUNT function while window function.

SELECT COUNT(1) OVER() AS total, 
       T1.* 
  FROM Usuario T1
 ORDER BY Id
OFFSET 1 ROWS
FETCH NEXT 2 ROWS ONLY
;

The OVER () is typically used to define the "window" (or range of rows / records) on the complete set of query results, over which the window function (here COUNT) will be applied.

In this case, since no partition was specified and no filter was applied on the User table, the COUNT function will be applied to the complete result set, ie it will return the total number of records in the User table.

The second alternative, more conventional, calculates the total separately.

SELECT  X.Total,
        T1.ID, 
        T1.Email, 
        T1.Nome, 
        T1.SobreNome
FROM Usuario T1 
CROSS JOIN ( SELECT COUNT(1) AS Total From Usuario ) X
ORDER BY T1.ID
OFFSET 1 ROWS
FETCH NEXT 2 ROWS ONLY
;

Although the result is the same in this particular case, I've changed here for CROSS JOIN instead of CROSS APPLY. I think not justified in this particular case, as CROSS APPLY is normally used when we want to establish a dependency between the two tables, or for example, apply a function to each row of results. Stayed in final answer just because I was playing with the fiddle and comparing the results of the two versions. I'm sorry for the same.

Returning to the answer, this second, perhaps more intuitive, way performs a Cartesian product between the result sets of T1 (Users) and X, ie, returns the result of combining each of the lines of the table T1 with the rows of the table X. As in this case X has only 1 record, the final result consists of the rows of the User table with an additional column that corresponds to the total of records.

This second alternative, depending on the size of the table, may be faster. But I like the first one the most :)

Stay the fiddle