CODEWITHSUNDEEP
sql-servert-sqlpagination
jackal
jackal

Reputation: 159

TSQL (SQL Server) Sorting and paging with row_number

I have a database (SQL Server) and app which fetches data and converts them into JSON.

I wrote a T-SQL query to order data by userid column (DESC) and take only first 10 rows, but it causes problem returning wrong results.

For example if I have following table:

      UserID
      ---
      User1
      User2
      User3
      ...
      User10
      ..
      User25

I want to to UserID to be DESC and get first ten results (then second ten results, etc). Simple saying I am looking for MySQL LIMIT substitute in SQL Server.

My query

SELECT * FROM 
   (SELECT  
       system_users_ranks.RankName,
       system_users.userid,
       system_users.UserName,
       system_users.Email, 
       system_users.LastIP, 
       system_users.LastLoginDate,
       row_number() OVER (ORDER BY system_users.userid) as myrownum
    FROM     
       system_users 
    INNER JOIN
       system_users_ranks 
       ON system_users.UserRank = system_users_ranks.rankid 
   ) as dertbl  
WHERE myrownum BETWEEN @startval AND @endval 
ORDER BY userid DESC

I can't move ORDER BY to inner SELECT.

Upvotes: 2

Views: 1005

Answers (1)

Quassnoi
Quassnoi

Reputation: 425471

You don't need it in the inner SELECT.

ROW_NUMBER has its own ORDER BY, and the final presentation is defined by the outermost ORDER BY anyway.

Your current query will work just fine.

Upvotes: 3

Related Questions