CONTAINSTABLE and ROW_NUMBER - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CONTAINSTABLE and ROW_NUMBER

Author

Topic

cab121
Starting Member

2 Posts

Posted - 2009-05-26 : 21:50:37

hi.

i am new to FULLTEXT queries and am having trouble understanding how I can get CONTAINSTABLE and ROW_NUMBER working in the same query.
basically i want a full text query with paging and have come up with the following...

SELECT (ROW_NUMBER() OVER (ORDER BY ct.[RANK] DESC)) AS RowNum, e.*
FROM CONTAINSTABLE (Stock, *, '( "acer" OR "monitor" )') As ct
JOIN Stock As e ON ct.[KEY] = e.StockID
WHERE ( (ct.[RANK] > 0) AND (RowNum >= 1) AND (RowNum < 21) )
ORDER BY ct.[RANK] DESC

i think that i cannot use RowNum in the WHERE clause for some reason but cannot work out how this should be done.

can anyone can point me in the right direction or sort out the above query for me. i have been trawling around for examples but cannot find what i am after (or maybe do not understand what i am seeing).

any help greatly appreciated.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-05-28 : 00:22:07

You cannot use that named column that you're creating in the same query, you need to either use a subquery, or a CTE or create a view.
The CTE would be something like this:


WITH RowsTable AS
(
    SELECT (ROW_NUMBER() OVER (ORDER BY ct.[RANK] DESC)) AS RowNum, e.* 
    FROM CONTAINSTABLE (Stock, *, '( "acer" OR "monitor" )') As ct 
    JOIN Stock As e ON ct.[KEY] = e.StockID
    WHERE (ct.[RANK] > 0)
)
SELECT *
FROM RowsTable
WHERE (RowNum >= 1) AND (RowNum < 21)
ORDER BY RowNum

cab121
Starting Member

2 Posts

Posted - 2009-05-30 : 00:49:50

thanks snSQL.
that is exactly what i was looking for but didn't know what to search for exactly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources