CODEWITHSUNDEEP
sqlsql-server
David
David

Reputation: 13

Trying to add a where clause for row_number

I am trying to add a where clause to the following query:

SELECT TOP 200 ROW_NUMBER() OVER (ORDER BY KEY_TBL.RANK DESC) AS RowNumber, FT_TBL.JobId, 
FT_TBL.Title, FT_TBL.[Description], FT_TBL.Location, KEY_TBL.RANK
FROM Jobs AS FT_TBL 
INNER JOIN 
FREETEXTTABLE (Jobs, (Title, [Description]), 'packaging')
AS KEY_TBL ON FT_TBL.JobId = KEY_TBL.[KEY]
WHERE CONTAINS (Location, '"luton*"')
      AND PostedDate >= GETDATE() - 7

What i want to add to the bottom is: AND RowNumber > 5, however it doesn't recognise RowNumber and I've tried adding it above the line that says INNER JOIN but it still doesn't work! Any ideas? Thanks

Upvotes: 1

Views: 961

Answers (1)

Kyle B.
Kyle B.

Reputation: 5787

Could you do?

SELECT * FROM (
SELECT TOP 200 ROW_NUMBER() OVER (ORDER BY KEY_TBL.RANK DESC) AS RowNumber, FT_TBL.JobId, 
FT_TBL.Title, FT_TBL.[Description], FT_TBL.Location, KEY_TBL.RANK
FROM Jobs AS FT_TBL 
INNER JOIN 
FREETEXTTABLE (Jobs, (Title, [Description]), 'packaging')
AS KEY_TBL ON FT_TBL.JobId = KEY_TBL.[KEY]
WHERE CONTAINS (Location, '"luton*"')
      AND PostedDate >= GETDATE() - 7
) as tmp WHERE tmp.RowNumber > 5

Upvotes: 2

Related Questions