473,385 Members | 1,492 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

union with Rank create duplicated records

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
2 2932
iburyak
1,017 Expert 512MB
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
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

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

Similar topics

10
by: KENNY L. CHEN | last post by:
Dear experts, I have two tables in my Oracle 8i database: TEST (COL1,COl2,REC_NO) and TEST1 (COL1,COL2,REC_NO). Both tables are unique-indexed on (COL1,COL2,REC_NO). I think the following...
9
by: (Pete Cresswell) | last post by:
I've got some SQL that works as far as returning a recordset from a series of UNION statements. viz: SELECT whatever UNION this UNION that UNION other
32
by: sandy | last post by:
I have a hobby website at: http://www.montana-riverboats.com which also resolves as: http://montana-riverboats.com ...without the www. One address has a Google page rank of three. The other...
2
by: Fred Zuckerman | last post by:
I have a union query joining two other queries, each with 62 fields. Interestingly, when I open the query it has the correct number of 2850 records. But if I then set a filter (using filter by...
14
by: Salad | last post by:
A97. Situation: I have 3 tables with a text field in each and a date field in the first 2 tables: Table1 Text1, Date1 Table2 Text2, Date2 Table3 Text3 (no date field) The following...
2
by: M Stery | last post by:
Have two base queries used for mailing labels with id,name,address,city,state,zip that are combined via a union query. One base query uses home address; the other base query uses biz address. I...
1
by: CrystalDBA | last post by:
I usually design applications in SQL Server and Crystal Reports. I now need to create a crystal report on an MS Access database. I have two tables: Services: Date datetime Entry text...
2
by: mattytee123 | last post by:
I have about 20 tables, of which I would like to do a union query and count of how many of each different code there is? The simplified verson of the table is structured like this. Code ...
2
by: Thomas | last post by:
Hi All. I think that if I explain the database layout first it may be easier to ask my question. Table Name Field Name Test TestCode Description SpecimenRequirements FeeSchedule TestCode...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.