union with Rank create duplicated records

[orenlevy1]

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,[key],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

[iburyak]

Try this:

[PHP]select max(rank), [key],PL -- you can change it to min(rank)
FROM(
select rank,[key],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%')
) a
GROUP by [key],PL
order by rank desc[/PHP]

[orenlevy1]

Works like a charm.

Thanks

Try this:

[PHP]select max(rank), [key],PL -- you can change it to min(rank)
FROM(
select rank,[key],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%')
) a
GROUP by [key],PL
order by rank desc[/PHP]