Unions and the order of things

[abraxas]

Hi people,
suffering from from an ordering dilemma in which using three queries (on same table) using Union all presents data in order in which the queries are executed and yet gives duplicates, presents the data in an ambiguous order format when using it as a sub query.

Here is the basic union query
Select * from products where
( BrandName Like '%fish%' AND BrandName Like '%oil%')
AND
( Keywords Like '%fish%' AND Keywords Like '%oil%')
AND
DisplayOnWeb = 1
union all
Select * from products where
( BrandName Like '%fish%' AND BrandName Like '%oil%')
or
( Keywords Like '%fish%' AND Keywords Like '%oil%')
AND
DisplayOnWeb = 1
union all
Select * from products where
( BrandName Like '%fish%' or BrandName Like '%oil%')
or
( Keywords Like '%fish%' or Keywords Like '%oil%')
AND DisplayOnWeb = 1

Works nicely, presenting data in row order, first from both fields (brandname & keywords), then from either field, followed
by OR'ing the lot, which puts the least relevent rows last.
BUT gives duplicate rows, of course.

Here is the the modified query prodkey being the unique identifier

Select * from products where prodkey in
(Select distinct prodkey from products where prodkey in
(Select prodkey from products where
( BrandName Like '%fish%' AND BrandName Like '%oil%')
and
( Keywords Like '%fish%' AND Keywords Like '%oil%')
AND DisplayOnWeb = 1

union all
Select prodkey from products where
( BrandName Like '%fish%' AND BrandName Like '%oil%')
or ( Keywords Like '%fish%' AND Keywords Like '%oil%')
AND DisplayOnWeb = 1
union all
Select prodkey from products where
( BrandName Like '%fish%' or BrandName Like '%oil%')
or ( Keywords Like '%fish%' or Keywords Like '%oil%') AND DisplayOnWeb = 1))

The rows returned do not take into account the order in which the queries are executed.

Anyway of preserving that order?

Thank you
abraxas

 

[mrdenny]

When using a Union All you will get the duplicates. Change the Union All to a UNION and you will not get duplicates.

I'm not sure what will happen to the data order.

The only way to guarantee the data order is to use an Order By clause.

Something like this might work for you.

select min(min_id), (Your other columns) from (
Select 1 min_id, * from products where 
( BrandName Like '%fish%' AND BrandName Like '%oil%') 
AND 
( Keywords Like '%fish%' AND Keywords Like '%oil%') 
AND 
DisplayOnWeb = 1 
union all 
Select 2, * from products where 
( BrandName Like '%fish%' AND BrandName Like '%oil%') 
or 
( Keywords Like '%fish%' AND Keywords Like '%oil%') 
AND
DisplayOnWeb = 1 
union all 
Select 3, * from products where 
( BrandName Like '%fish%' or BrandName Like '%oil%') 
or 
( Keywords Like '%fish%' or Keywords Like '%oil%') 
AND DisplayOnWeb = 1
) a
group by All your columns.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com