SQL - Order after filtering

Question

How can I order the data and then filter it in TSQL (SQL Server)?

I've tried something like this:

SELECT [Job].*, 
    ROW_NUMBER() OVER (ORDER BY [Job].[Date]) AS RowNum 
FROM [Job] 
ORDER BY Rank 
WHERE RowNum >= @Start AND RowNum < @End

Doesn't work. I also tried to use a subquery, which throws:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

I don't want to use TOP or FOR XML.

How to solve this?

Answer 1

Use a CTE. Note, the "inner" ORDER BY in this case is implied by the ROW_NUMBER/OVER.

;WITH cBase AS
(
SELECT
    [Job].*, 
    ROW_NUMBER() OVER (ORDER BY [Job].[Date]) AS RowNum 
FROM
    [Job] 
)
SELECT
    *
FROM
    cBase
WHERE
    RowNum >= @Start AND RowNum < @End
--ORDER BY
    --output order

Edit:

Your search between @Start and @End is on the sequence generated by the ROW_NUMBER on date. Rank has no relation to this sequence. Rank (assuming it's a column in the table) will be ignored because your sequence is on Date. You don't need to sort on it.

If "rank" is actually "RowNum" then you still don't need an "inner" sort because it's a set operation. You'll need it on the outermost sort though.

If rank is a secondary sort on Date then use this:

ROW_NUMBER() OVER (ORDER BY [Job].[Date], [Job].[Rank]) AS RowNum