Solved

MS SQL Pagination for Complex Query

Posted on 2016-11-22
5
69 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

752 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