question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked MelissaMa-msft edited

How to make pagination by using offset when group by ?

I work on sql server 2012 i face issue I can't using offset to make pagination
when make group by
my query as below

 declare @ItemId  int = 2
 declare @InventoryLocation int=1
 declare @StartDate date='1900-01-01'
 declare @EndDate date='01-01-2100'
 declare @OptionId int=1
 declare @PageNo INT=1
 declare @RowCountPerPage INT=10
    
 SELECT i.itemName,l.InventoryName, SUM(case when QTY > 0  then QTY else 0 end)  as PurchasedItem,SUM(case when QTY < 0  then -QTY else 0 end)  as ConsumItems,SUM(case when QTY > 0 then QTY else 0 end) + SUM(case when QTY < 0 then QTY else 0 end) as remaining 
 FROM [dbo].[Invenroty] n with(nolock)
 inner join [dbo].[InventoryLocations] l with(nolock) on l.id=n.InventoryLocID
 inner join [dbo].[Items] i with(nolock) on n.itemid=i.id
 inner join [dbo].[TransactionTypes] t with(nolock) on n.transactionTypeId=t.ID and InventoryLocID=case when @InventoryLocation is null then n.InventoryLocID else @InventoryLocation end
 and i.id=case when @ItemId is null then n.itemid else @ItemId end 
 and PostingDate between   case when @StartDate <>'1900-01-01' then @StartDate else '1900-01-01' end
 and  case when @EndDate <>'2100-01-01' then @EndDate else '2100-01-01' end  
    
    
 GROUP BY i.itemName,l.InventoryName

i need to add below block

   OFFSET (@PageNo - 1) * @RowCountPerPage ROWS
   FETCH NEXT @RowCountPerPage ROWS ONLY

so how to make pagination on query above using offset


sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ahmedsalah-1628,

Could you please validate all the answers so far and provide any update?

Thanks.

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @ahmedsalah-1628,

Please refer below and check whether it is working.

 declare @ItemId  int = 2
 declare @InventoryLocation int=1
 declare @StartDate date='1900-01-01'
 declare @EndDate date='2100-01-01'
 declare @OptionId int=1
 declare @PageNo INT=1
 declare @RowCountPerPage INT=10
         
 SELECT i.itemName,l.InventoryName
 ,SUM(case when QTY > 0  then QTY else 0 end) over (partition by i.itemName,l.InventoryName)  as PurchasedItem
 ,SUM(case when QTY < 0  then -QTY else 0 end) over (partition by i.itemName,l.InventoryName) as ConsumItems
 ,SUM(case when QTY > 0 then QTY else 0 end) over (partition by i.itemName,l.InventoryName)+ SUM(case when QTY < 0 then QTY else 0 end) over (partition by i.itemName,l.InventoryName) as remaining 
 FROM [dbo].[Invenroty] n with(nolock)
 inner join [dbo].[InventoryLocations] l with(nolock) on l.id=n.InventoryLocID
 inner join [dbo].[Items] i with(nolock) on n.itemid=i.id
 inner join [dbo].[TransactionTypes] t with(nolock) on n.transactionTypeId=t.ID and InventoryLocID=case when @InventoryLocation is null then n.InventoryLocID else @InventoryLocation end
 and i.id=case when @ItemId is null then n.itemid else @ItemId end 
 and PostingDate between   case when @StartDate <>'1900-01-01' then @StartDate else '1900-01-01' end
 and  case when @EndDate <>'2100-01-01' then @EndDate else '2100-01-01' end     
 order By  itemName,InventoryName
 OFFSET (@PageNo - 1) * @RowCountPerPage ROWS
 FETCH NEXT @RowCountPerPage ROWS ONLY 

OR

 declare @ItemId  int = 2
 declare @InventoryLocation int=1
 declare @StartDate date='1900-01-01'
 declare @EndDate date='2100-01-01'
 declare @OptionId int=1
 declare @PageNo INT=1
 declare @RowCountPerPage INT=10
     
 ;with cte as (
 SELECT i.itemName,l.InventoryName, SUM(case when QTY > 0  then QTY else 0 end)  as PurchasedItem,SUM(case when QTY < 0  then -QTY else 0 end)  as ConsumItems,SUM(case when QTY > 0 then QTY else 0 end) + SUM(case when QTY < 0 then QTY else 0 end) as remaining 
 FROM [dbo].[Invenroty] n with(nolock)
 inner join [dbo].[InventoryLocations] l with(nolock) on l.id=n.InventoryLocID
 inner join [dbo].[Items] i with(nolock) on n.itemid=i.id
 inner join [dbo].[TransactionTypes] t with(nolock) on n.transactionTypeId=t.ID and InventoryLocID=case when @InventoryLocation is null then n.InventoryLocID else @InventoryLocation end
 and i.id=case when @ItemId is null then n.itemid else @ItemId end 
 and PostingDate between   case when @StartDate <>'1900-01-01' then @StartDate else '1900-01-01' end
 and  case when @EndDate <>'2100-01-01' then @EndDate else '2100-01-01' end     
 GROUP BY i.itemName,l.InventoryName)
 select * from cte
 order By  itemName,InventoryName
 OFFSET (@PageNo - 1) * @RowCountPerPage ROWS
 FETCH NEXT @RowCountPerPage ROWS ONLY 

OR

 declare @ItemId  int = 2
 declare @InventoryLocation int=1
 declare @StartDate date='1900-01-01'
 declare @EndDate date='2100-01-01'
 declare @OptionId int=1
 declare @PageNo INT=1
 declare @RowCountPerPage INT=10
    
 ;with cte as (
 SELECT i.itemName,l.InventoryName,Qty
 FROM [dbo].[Invenroty] n with(nolock)
 inner join [dbo].[InventoryLocations] l with(nolock) on l.id=n.InventoryLocID
 inner join [dbo].[Items] i with(nolock) on n.itemid=i.id
 inner join [dbo].[TransactionTypes] t with(nolock) on n.transactionTypeId=t.ID and InventoryLocID=case when @InventoryLocation is null then n.InventoryLocID else @InventoryLocation end
 and i.id=case when @ItemId is null then n.itemid else @ItemId end 
 and PostingDate between   case when @StartDate <>'1900-01-01' then @StartDate else '1900-01-01' end
 and  case when @EndDate <>'2100-01-01' then @EndDate else '2100-01-01' end     
 order By  itemName,InventoryName
 OFFSET (@PageNo - 1) * @RowCountPerPage ROWS
 FETCH NEXT @RowCountPerPage ROWS ONLY )
 select itemName,InventoryName,SUM(case when QTY > 0  then QTY else 0 end)  as PurchasedItem,SUM(case when QTY < 0  then -QTY else 0 end)  as ConsumItems,SUM(case when QTY > 0 then QTY else 0 end) + SUM(case when QTY < 0 then QTY else 0 end) as remaining 
  from cte 
  GROUP BY itemName,InventoryName

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

You need to add an ORDER BY as well. OFFSET-FETCH is part of the ORDER BY clause.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ok but how to handle group by
the issue i face here that im use group by
if normal select i can write order by
but i use group by

0 Votes 0 ·

To use OFFSET-FETCH you need to use ORDER BY, that's the way it is. And of course, without an ORDER BY they don't make much sense, since to be meaningful, OFFSET-FETCH needs a well-defined order, and without ORDER BY you have an unordered set.

The good news is that it is perfectly legal to use both GROUP BY and ORDER BY, so just your add an ORDER BY for whatever you want to order by.

0 Votes 0 ·