MS SQL Pagination for Complex Query

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

LVL 10
skijAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
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
 
Russ SuterCommented:
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
 
skijAuthor Commented:
Where should that be added in my complex query?
0
 
Pawan KumarDatabase ExpertCommented:
Hi skij3h,
I think you should have also selected Russ Suter's comment as the solution.

He has also helped you.

Regards,
Pawan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.