Solved

MS SQL Pagination for Complex Query

Posted on 2016-11-22
5
50 Views
Last Modified: 2016-11-23
Using MS SQL 2008, how can I get the results, 20 through 30?  I don't want the results before the 20th result and I only want ten results to be returned.

SELECT a.asset_id as _id, a.Name as name, f.Name type, a.Update_Date as timestamp, a.Rating as rating, a.Filesize as size, p.Name as project, rank FROM (SELECT a.asset_id, a.Name, a.Media_Type, a.Update_Date, a.Rating, a.Filesize, a.ProjectID, ipmrank.rank From ipm_asset a INNER JOIN ##AssetsBySearchTerm_GAJY97B70A3ZG as ipmrank on a.asset_id = ipmrank.[KEY] where 1=1 and a.asset_id in ( SELECT asset_id FROM IPM_asset a WHERE a.available = 'Y' and asset_id in (select a.asset_id from asset_filetype_lookup a where a.convertertype in (4 )) ) and a.type <> 84 and a.type <> 85 and a.type <> 48 and a.type <> 64 and a.available = 'Y' /*end*/ ) a JOIN IPM_PROJECT as p ON a.ProjectID = p.ProjectID JOIN IPM_FILETYPE_LOOKUP as f ON a.Media_Type = f.Media_Type join (select asset_id from ipm_asset_security Where ((security_id in (select a.groupid from ipm_group a, ipm_group_user b where a.active = 'Y' and a.groupid = b.groupid and b.userid = '1') and type = 'G')) or (security_id = '1' and type = 'U') union select asset_id from ipm_asset a where a.securitylevel_id >= '0' ) d on a.asset_id = d.asset_id ORDER BY a.Name ASC

Open in new window

0
Comment
Question by:skij
  • 2
5 Comments
 
LVL 20

Assisted Solution

by:Russ Suter
Russ Suter earned 250 total points
ID: 41897923
SQL Server 2008 and above make this really easy. Just append the following to your existing query:
  OFFSET @PageSize * (@PageNumber - 1) ROWS
  FETCH NEXT @PageSize ROWS ONLY;

Open in new window

All you need to do is provide the values for @PageSize and @PageNumber so if you want rows 21 thru 30 you'd do this
SET @PageSize = 10
SET @PageNumber = 3

Open in new window

BTW, mathematically, 20 thru 30 is 11 rows.
0
 
LVL 10

Author Comment

by:skij
ID: 41897989
Where should that be added in my complex query?
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 250 total points
ID: 41898264
try.. <<Adding to the above solution.>>

DECLARE @PageSize AS INT = 10
DECLARE @PageNumber AS INT = 3

SELECT * FROM
(
	SELECT a.asset_id as _id, a.Name as name, f.Name type, a.Update_Date as timestamp, a.Rating as rating, a.Filesize as size, p.Name as project, 
	rank FROM (SELECT a.asset_id, a.Name, a.Media_Type, a.Update_Date, a.Rating, a.Filesize, a.ProjectID, ipmrank.rank From ipm_asset a 
	INNER JOIN ##AssetsBySearchTerm_GAJY97B70A3ZG as ipmrank on a.asset_id = ipmrank.[KEY] where 1=1 and a.asset_id in 
	( SELECT asset_id FROM IPM_asset a WHERE a.available = 'Y' and asset_id in (select a.asset_id from asset_filetype_lookup a where a.convertertype in (4 )) ) 
	and a.type <> 84 and a.type <> 85 and a.type <> 48 and a.type <> 64 and a.available = 'Y' /*end*/ ) a JOIN IPM_PROJECT as p ON a.ProjectID = p.ProjectID 
	JOIN IPM_FILETYPE_LOOKUP as f ON a.Media_Type = f.Media_Type join (select asset_id from ipm_asset_security 
	Where ((security_id in (select a.groupid from ipm_group a, ipm_group_user b where a.active = 'Y' and a.groupid = b.groupid and b.userid = '1') and type = 'G')) 
	or (security_id = '1' and type = 'U') 
	union select asset_id from ipm_asset a where a.securitylevel_id >= '0' ) d 
	on a.asset_id = d.asset_id 
)p
ORDER BY a.Name ASC
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

Open in new window

0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41898284
Hi skij3h,
I think you should have also selected Russ Suter's comment as the solution.

He has also helped you.

Regards,
Pawan
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question