CODEWITHSUNDEEP
sqlsql-server
Hassan Al Bourji
Hassan Al Bourji

Reputation: 195

Select a range of data from SQL query

I'm using SQL Server to to query some rows from a table, and I need to return the elements between rows 20-30 how is this possible?

This is my query:

select * 
from PostsView 
where EnumKey = " & CategoryId & "
  and IsDeleted = 'False'
order by CreationDate desc

Please help me

Upvotes: 0

Views: 80

Answers (2)

APH
APH

Reputation: 4154

You can assign row numbers, then select rows 20-30 (assuming you want rows 20-30 after your where clause is applied, not based on the complete contents of the table).

;With CTE as (
  select *, row_number over (order by CreationDate Desc) as RN 
  from PostsView 
  where EnumKey = " & CategoryId & "
  And IsDeleted = 'False')

Select * from CTE where RN between 20 and 30

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

One method:

select TOP 11 *
from  (select TOP 30 *
       from PostsView 
       where EnumKey = " & CategoryId & " And IsDeleted = 'False'
       Order By CreationDate Desc
      ) t
Order By CreateDate ASC;

(You can use another subquery to reverse the order again.)

Or, in a more recent version of SQL Server:

OFFSET 19 FETCH NEXT 11 ROWS

Or, using row_number().

Upvotes: 1

Related Questions