tags:

views:

80

answers:

1

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

+2  A: 

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
Kyle B.
+1 Though you really should keep the TOP 200 in the outer query ;-)
Andomar
that worked! thanks so much!
David
iv taken it out to top query , thanks
David
Are you sure? Wouldn't that be less efficient? I believe that would return the entire result-set (of potentially hundreds of thousands), then select the first 200 records. My solution obtains only 200 records, then selects those with rownumber > 5.
Kyle B.
ok saying that, makes sense, i put the top 200 statement in the inner query again
David
Results are indeterminate when there is a TOP without an ORDER BY clause on the select. The ORDER BY on the ROW_NUMBER function does not guarentee order of the results.
Shannon Severance
@KyleB: SQL Server is slightly more clever than that. It takes the SQL and compiles it. If you look at the execution plan you'll see it is related to but often not much like the query you wrote. In this case the optimiser Would be able to recognise the inneficiency you're worried about and cater for it. Often, the best bet for this is normally to run some tests and see the difference.
Dems