By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,131 Members | 1,072 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,131 IT Pros & Developers. It's quick & easy.

union with Rank create duplicated records

P: 3
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, Im sure there is something better.
Thanks in advance

Oren Levy
Dec 7 '06 #1
Share this Question
Share on Google+
2 Replies


iburyak
Expert 100+
P: 1,017
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]
Dec 7 '06 #2

P: 3
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]
Dec 13 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.