Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS SQL Pagination for Complex Query

Posted on 2016-11-22
5
Medium Priority
?
90 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
4 Comments
 
LVL 20

Assisted Solution

by:Russ Suter
Russ Suter earned 1000 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 38

Accepted Solution

by:
Pawan Kumar earned 1000 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 38

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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

564 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