CODEWITHSUNDEEP
sql-server
IceCode
IceCode

Reputation: 1751

Select specific amount of rows from query

I have this query below and I need to get specific amounts of rows from it also, like 100 rows. I also need to get the rows from a specific row count like from 100 to 200, from 200 - 300 etc. I have been experimenting with ROW_NUMBER() function without luck.

NB! Cannot us "offset 100 rows fetch next 100 rows only" because it only works in mssql 2012.

SELECT 
      Result.CaseCount,
      CaseID, 
      ChildCaseName,
      CPR,
      FirstName,
      LastName, 
      CustomerInfoID, 
      InstitutionName, 
      CaseDeadline, 
      [StatusName], 
      [StatusOwner] 
FROM ResultSelectorCTE, 
    (select count(*) as CaseCount FROM ResultSelectorCTE) AS Result

How can I do this?

Upvotes: 2

Views: 3691

Answers (4)

Marc Gravell
Marc Gravell

Reputation: 1062502

I also need to get the rows from a specific row count like from 100 to 200, from 200 - 300 etc.

select Foo, Bar
from SomeTable
order by Blap
offset 100 rows fetch next 100 rows only

Or to do it with ROW_NUMBER():

select x.Foo, x.Bar
from (
    select Foo, Bar, ROW_NUMBER() over(order by Blap desc) as [Row]
    from SomeTable) x
where x.[Row] > 100 and x.[Row] <= 200
order by x.[Row]

Upvotes: 5

Sachu
Sachu

Reputation: 7766

Select TOP 100 [column1],[column2].....FROM TABLE

but it will depends on the query you are giving. If no orderby is there it will pull different result each time..

Upvotes: 0

nvoigt
nvoigt

Reputation: 77285

SELECT TOP X Result.CaseCount, CaseID, ChildCaseName, ...

Where X is your number.

Upvotes: 1

fubo
fubo

Reputation: 45947

MSSQL: SELECT TOP 100 Result.CaseCount,...

MYSQL: SELECT ... LIMIT 100;

Upvotes: 3

Related Questions