CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Nock
Nock

Reputation: 6609

TSQL OrderBy Offset issue

I have this query that almost makes what I want:

SET NOCOUNT ON

SELECT D.Id from MtdMdl_HierarchicalObject D
where D.Name LIKE '%R%'

SELECT B.* FROM MtdMdl_Item A
OUTER apply (SELECT * FROM MtdMdl_Item as C where a.Id=c.Id) as B
WHERE A.Owner in 
(
    SELECT D.Id from MtdMdl_HierarchicalObject D
    where D.Name LIKE '%R%'
)
order by Id offset 0 ROWS FETCH NEXT 3 ROWS ONLY
GO

Unfortunately I would like the Order By Id Fetch New 3 Rows to be applied on each object returned by:

SELECT D.Id from MtdMdl_HierarchicalObject D
where D.Name LIKE '%R%'

Because the current script apply the Paging on the whole result of the previous statement.

Upvotes: 0

Views: 94

Answers (1)

M.Ali
M.Ali

Reputation: 69554

If you are not skipping any row and just getting 3 rows why bother using OFFSET at all ??? just Use TOP clause 

SELECT B.* 
FROM MtdMdl_Item A OUTER APPLY 
                    (SELECT TOP 3 * 
                     FROM MtdMdl_Item  
                      where Id= a.Id) as B
WHERE A.Owner in 
(
    SELECT D.Id from MtdMdl_HierarchicalObject D
    where D.Name LIKE '%R%'
)
order by Id

In this case you could do something like this...

SELECT * FROM
(
SELECT B.*, A.ID , RN = ROW_NUMBER() OVER (PARTITION BY A.ID ORDER BY A.ID) 
FROM MtdMdl_Item A OUTER APPLY 
                    (SELECT  * 
                     FROM MtdMdl_Item  
                      where Id= a.Id) as B
WHERE A.Owner in 
(
    SELECT D.Id from MtdMdl_HierarchicalObject D
    where D.Name LIKE '%R%'
)
order by Id
) Q
WHERE RN >= LowerList  AND RN <= UpperLimit

Upvotes: 1

Related Questions