1

I have a table: id, Group, Status, Subject.

I need rows where group=2 AND status=2, but I want to skip the first 8 results (ordered by id). This is what I tried, but it does not skip the first 8 rows:

SELECT TOP 3 [id], LEFT ([subject],30) AS Title 
FROM [Katavot] 
    WHERE (([Status] = @Status) AND ([Group] = @Group)) 
ORDER BY [id] DESC
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
amid
  • 23
  • 6

3 Answers3

1
;WITH Katavod_Modified AS 
(
    SELECT id, LEFT ([subject],30) As Subject, [Group], [Status],  ROW_NUMBER() OVER (ORDER BY id) RN
    FROM [Katavot]
)
SELECT TOP 3 [id], LEFT ([subject],30) AS Title FROM [Katavod_Modified] WHERE 
(([Status] = @Status) AND ([Group] = @Group) AND (RN > 8)) ORDER BY [id] DESC

Looks very similar to this one.

Community
  • 1
  • 1
Pavel Nefyodov
  • 876
  • 2
  • 11
  • 29
1

Based on row_number

WITH MyResult AS
{
SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS RowNumber, [id], LEFT ([subject],30) AS Title 
FROM [Katavot] 
    WHERE (([Status] = @Status) AND ([Group] = @Group)) 
ORDER BY [id] DESC
}
SELECT [id], Title 
FROM MyResult
WHERE RowNumber BETWEEN 9 AND 12
-1

MySQL:

LIMIT x, y

MSSQL:

select top y *
from <table>
where <unique id> not in (select top x <unique id> from <table> order by <unique id>)
from <table> order by <unique id>
NaN
  • 8,596
  • 20
  • 79
  • 153