June 25, 2009 at 11:34 am
How do I 'take' the results with the largest rank or any rank above 100 in a free text search?
SELECT
ftt.RANK,
table.ID,
table.NAME
FROM table
INNER JOIN
FREETEXTTABLE
(
table, Name,
'Search phrase'
)
as ftt
ON
ftt.=table.ID
WHERE ftt.RANK = (SELECT MAX(ftt.RANK) FROM ftt) OR ftt.RANK > '100'
ORDER BY ftt.RANK DESC
Right now it says, "invalid object name 'ftt' "
It doesn't like my subquery.
June 25, 2009 at 12:20 pm
Can you remove all of the group by stuff and create a derived table on your query, and then reference 'Rank'?
June 25, 2009 at 12:46 pm
Are you talking about making a temp table? Not sure what you mean by removing group by.
June 25, 2009 at 12:48 pm
No, like:
SELECT RANK, col2, col3, etc.
FROM
(SELECT *
FROM yourquery
) as tbl
WHERE RANK > 100
June 25, 2009 at 1:35 pm
I tried referencing as you mentioned, and as before I can reference RANK with no problem, but I can't get a reference to the max value of RANK into the where clause.
June 25, 2009 at 1:38 pm
That might require a HAVING clause, which filters the aggregate. Can you post whatcha got now?
June 25, 2009 at 2:38 pm
Your example, looks like it has two wrappings. The code below seem to work exactly the same as the code in my original post without the two wrappers tbl and tbl2. I'm guessing that I might not need the extra layers. Or else I'm not fully understanding what you were thinking.
SELECT Ranking, col1, col2 FROM
(
SELECT * FROM
(SELECT
ftt.RANK as Ranking,
table.col1,
table.col2
FROM tblOrganization
INNER JOIN
FREETEXTTABLE
(
table, col2,
'search string'
)
as ftt
ON
ftt.=table.col1)as tbl ) as tbl2
WHERE Ranking > 100
Both this (derived from your example) and my previous example work fine if I don't bother put in the MAX(RANK) condition anywhere.
The problem has always been running comparisons with a MAX(RANK). For example:
HAVING MAX(Ranking) = Ranking
This yields:
'Column 'tbl2.Ranking' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.'
As a side note, can I have the two clauses HAVING and WHERE as separated by an OR statement?
June 26, 2009 at 9:15 am
Thanks ab5sr,
I have a slack working solution though not succinct and efficient.
What I did was:
DECLARE @FullResults TABLE
(
Ranking INT,
ID varchar(10),
Name varchar(100)
)
DECLARE @MaxNum INT;
INSERT INTO @FullResults
SELECT * FROM myoriginalquery
SET @MaxNum = (SELECT MAX(Ranking) FROM @FullResults)
SELECT * FROM @FullResults
WHERE Ranking = @MaxNum OR Ranking > 100
ORDER BY Ranking DESC
June 26, 2009 at 9:22 am
Ok cool. Glad you found something that works for you.
Interestingly, I just saw a jumpstart on this today. I haven't taken a look at this, but I know that the guy is sharp. Check it and see if it's related:
Ordering Full Text Index Search Results by Relevance
By Robert Cain
http://www.jumpstarttv.com/ordering-full-text-index-search-results-by-relevance-_555.aspx
Good luck bro
June 26, 2009 at 12:22 pm
Oh, thanks! I'll check that out.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply