December 7, 2006 at 1:48 pm
Hi Everyone.
I have a problem that I could not figure out what to do with it.
I have a couple of tables and views. All have the same unique ID.
When a user try to do a search on our web site we want to try to look in multiple tables and views, Rank the results and aggregate the results back (remove any duplicates).
Part of the search is a Full Text Index search.
I created already the following query that seems to be working ok except for one problem, some of the results are duplicated because the Rank is unique.
select rank,,PL
From freetexttable (dbo.items,*,'Key Words') as ft
join Items as I on ft.[Key] = I.ItemNumber
LEFT OUTER JOIN dbo.IM_90_UDF_IMMasterfile ON ft.[Key]= dbo.IM_90_UDF_IMMasterfile.ItemNumber
Where (I.Approved = 1 and dbo.IM_90_UDF_IMMasterfile.Web_Exclude<>'Y')
union
select '999999',ItemNumber,PL
from View_Items_Published
Where (Mfg_Part_Number like '%Key Words%')
union
select '999998',ItemNumber,PL
from View_Items_Published
Where (ItemNumber like '%Key Words%')
order by rank desc
In a way to fix this issue I was suggested to create a Temp table, insert the data from all tables / views into it and group the results.
The problem with that is I have to Drop the table before/after every search. And lots of other issues (table locks etc’)
Here is the script for this one:
Drop table #QueryResult
CREATE TABLE #QueryResult (Rank int,ItemNumber char(15), PL Char(4))
INSERT #QueryResult (Rank ,ItemNumber , PL)
select rank,I.ItemNumber ,PL
From freetexttable (dbo.items,*,'Key Words') as ft
join Items as I on ft.[Key] = I.ItemNumber
LEFT OUTER JOIN dbo.IM_90_UDF_IMMasterfile ON ft.[Key]= dbo.IM_90_UDF_IMMasterfile.ItemNumber
Where (I.Approved = 1 and dbo.IM_90_UDF_IMMasterfile.Web_Exclude<>'Y')
INSERT #QueryResult
select '999999',ItemNumber,PL
from View_Items_Published
Where (Mfg_Part_Number like '%Key Words%')
INSERT #QueryResult
select '999998',ItemNumber,PL
from View_Items_Published
Where (ItemNumber like '%Key Words%')
Select ItemNumber, Max(Rank) as Rank, PL
From #QueryResult
Group by ItemNumber, PL
order by rank desc
My question is:
Any way of aggregating all the results on the fly, I’m sure there is something better.
Thanks in advance
Oren Levy
December 7, 2006 at 2:58 pm
Derived table to assemble the ranking, outer query on the derived table to find the max rank for each group and sort on it:
Select ItemNumber, PL, Max(Rank)
From
(
select rank, As ItemNumber, PL
From freetexttable (dbo.items,*,'Key Words') as ft
join Items as I on ft.[Key] = I.ItemNumber
LEFT OUTER JOIN dbo.IM_90_UDF_IMMasterfile ON ft.[Key]= dbo.IM_90_UDF_IMMasterfile.ItemNumber
Where (I.Approved = 1 and dbo.IM_90_UDF_IMMasterfile.Web_Exclude<>'Y')
union All
select '999999',ItemNumber,PL
from View_Items_Published
Where (Mfg_Part_Number like '%Key Words%')
union All
select '999998',ItemNumber,PL
from View_Items_Published
Where (ItemNumber like '%Key Words%')
) dt
Group By ItemNumber, PL
Order By Max(Rank) Desc
December 7, 2006 at 6:33 pm
you are unbelievable
Thank you so much, you have no idea how great this is.
You rock.....
Oren Levy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply