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.
| Author |
Topic |
|
eric33
Starting Member
4 Posts |
 Posted - 2007-08-30 : 15:57:33
|
| I am attempting to return ranked unique pages by searching 3 columns Title, Keyword, and FullPageText. My query is returning the results ranked properly, however because a hit can take place in the Keyword column as well as the FullPageText column or Title Column I am getting duplicate or triplicate entries.For example here is an example scenario DBPageID | TITLE | KEYWORDS | FULL TEXT-------------------------------------------1 Home Home Page Welcome to the Home Page!2 x x xSearching for 'Home' on this db will result in 3 results they way I have written my query, but I only want it to return the PageID once.Here is my select statement.SELECT row_number() over(order by Results.rnk)[rank],p.ID, p.Title, p.Url FROM Pages AS p INNER JOIN (SELECT *,3 rnk FROM freetexttable(Pages, ([FullPageText]), @SearchText) AS A UNION ALL SELECT *,2 rnk FROM freetexttable(Pages, ([Keywords]), @SearchText) AS B UNION ALL SELECT *,1 FROM freetexttable(Pages, ([Title]), @SearchText) AS C) AS Results ON p.ID = Results.[key] ORDER BY [rank] Any help would be GREATLY appreciated! |
|
|
Koji Matsumura
Posting Yak Master
141 Posts |
Posted - 2007-08-30 : 21:31:22
|
| change UNION ALL to UNION? |
 |
|
|
|
|
|
|
|
Please help with unique FTS statement.