selecting the next set of top 30 records

Question

This is my query:

SELECT Top 30 *  
FROM  (SELECT *,  Row_number() OVER( PARTITION BY EntityPicURL 
                                     ORDER BY FavoriteCount desc) AS RN
       FROM   TweetEntity   
       WHERE HashTag LIKE '%%23RIPOlgaSyahputra%') A
WHERE  RN = 1 
ORDER BY FavoriteCount desc , LastModifieddateTime desc

This will select the first 30 unique records of the column entitypicURl. Now that I want to select the next 30 records (31-60).

This is a sort of a query i used earlier but this returned many duplicate entries of entitypicURL.

select * 
from (select *, row_no = row_number() over (order by FavoriteCount desc,
                                                     LastModifiedDateTime desc)
      from TweetEntity 
      where HashTag like '%%23RIPOlgaSyahputra%') e 
where e.row_no > 30 and e.row_no <=60

Now i want to combine the value of first query and include e.row_no>30 and e.row_no<60 from the second query.

It is not a duplicate. My confusion is just combining two queries because both has got row_numbers.

possible duplicate of [What is the best way to paginate results in SQL Server](http://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server) — Pred, Mar 27 '15 at 13:02
See https://technet.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx — Andy Nichols, Mar 27 '15 at 13:03
It is not a duplicate. My confusion is just combining two queries because both has got row_numbers — Venkat, Mar 27 '15 at 13:07
Please post the results you are getting from the query you tried, and how they are different from the results you want. — Tab Alleman, Mar 27 '15 at 13:17

Giorgos Betsos · Accepted Answer · 2015-03-27T13:25:15.207

1

You can use nested CTEs like this:

;WITH CTE1 AS (
   SELECT *,  
          ROW_NUMBER() OVER(PARTITION BY EntityPicURL
                            ORDER BY FavoriteCount desc) AS RN
   FROM   TweetEntity   
   WHERE HashTag like '%%23RIPOlgaSyahputra%'
), CTE2 AS (
   SELECT *,
          ROW_NUMBER() OVER (ORDER FavoriteCount DESC, 
                                   LastModifiedDateTime DESC) AS row_no    
   FROM CTE1
   WHERE RN = 1
)
SELECT *
FROM CTE2 
WHERE row_no > 30 and row_no <=60

CTE2 will apply ROW_NUMBER on the filtered by RN = 1 result set of CTE1.

edited Mar 27 '15 at 13:25

answered Mar 27 '15 at 13:22

Giorgos Betsos

71,379
9
63
98

Fantastic. This is what I was looking for. A query with in a query. It worked perfectly. Thanks. – Venkat Mar 27 '15 at 13:25

Khurram Ali · Answer 2 · 2015-03-27T13:15:16.407

You can Use OFFSET to fetch records like this

you can select 30 records from below query

Select * FROM   TweetEntity
ORDER BY FavoriteCount  desc OFFSET 0 ROWS FETCH NEXT 30 ROWS ONLY

you can select next 30 records from below query

 Select * FROM   TweetEntity
ORDER BY FavoriteCount  desc OFFSET 30  ROWS FETCH NEXT 30 ROWS ONLY

Note: OFFSET Will work on Sql server 2012+

Limitations in Using OFFSET-FETCH

ORDER BY is mandatory to use OFFSET and FETCH clause.
OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
TOP cannot be combined with OFFSET and FETCH in the same query expression.
The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

If you look at my second query It has got the pagination. My question is however getting the first query into pagination as I am not able to merge two row_numbers properly — Venkat, Mar 27 '15 at 13:15

selecting the next set of top 30 records

2 Answers2