Solved

MS SQL Pagination for Complex Query

Posted on 2016-11-22
5
31 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 17

Accepted Solution

by:
Pawan Kumar Khowal 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 17

Expert Comment

by:Pawan Kumar Khowal
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

707 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now