Duplicated Records from Union - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Duplicated Records from Union

Author

Topic

Mortifier
Starting Member

11 Posts

Posted - 2003-08-03 : 02:43:06

I have a Product table with each item containing 2 price fields. I have a stored procedure with a UNION where I pull certain products with Price1 AS Price and some other Products (that meet a certain criteria) as Price2 AS Price.

The problem that I'm having is that the UNION is including the same product in a recordset with 2 different prices.

I tried using NOT IN which works, but the execution time of the stored procedure with comparing values for all products leads to ASP timeouts.

Any suggestions for a more efficent way to eliminate the duplicates?

Thanks,
Kevin

Nazim
A custom title

1408 Posts

Posted - 2003-08-03 : 03:09:56

Try NOT EXISTS instead of NOT IN.

How huge is your data?.

Create appropriate indexes , update your stats and see if it helps.

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-03 : 03:33:30

You could try this:


SELECT Product, MIN(Price)
FROM 
(
  SELECT ProductName AS Product, Price1 AS Price FROM Products
         WHERE CertainCriteria

  UNION ALL

  SELECT ProductName, Price2 FROM Products
         WHERE OtherCriteria
) A
GROUP BY Product

UNION ALL is likely to be much faster than UNION for large result-sets, since it does not incur the overhead of filtering duplicates, we do that on the final results using a group-by. Of course, you could use any Aggregate function apart from MIN, but I suppose customers always want the cheapest price

Like Nazim suggested, also look at appropriate indexes, and update statistics, that could give you a real perf boost.

Owais

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-03 : 09:50:28

By the way, look at the OR operator instead of using a UNION.

i.e., instead of

select * from tbl where a = 10
union all
select * from tbl where a = 20

try

select * from tbl where a = 10 or a = 20

or even

select * from tbl where a in (10,20)

If you can do that, your solution might be:

SELECT Product, MIN(Price)
FROM
Products
WHERE CertainCriteria OR OtherCriteria
GROUP BY Product

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-03 : 11:55:22

Jeff, don't you mean:

SELECT Product, MIN(CASE WHEN CertainCriteria THEN Price1 ELSE Price2 END)
FROM
Products
WHERE CertainCriteria OR OtherCriteria
GROUP BY Product

?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-03 : 12:17:47

If, when both criteria hold, you want one criterion to override the other, you can adapt Owais' UNION ALL query like this:


SELECT Product, COALESCE(MIN(Price1), MIN(Price2)) AS Price
FROM 
(
  SELECT ProductName AS Product, Price1, NULL AS Price2 FROM Products
         WHERE CertainCriteria

  UNION ALL

  SELECT ProductName, NULL, Price2 FROM Products
         WHERE OtherCriteria
) A
GROUP BY Product

Mortifier
Starting Member

11 Posts

Posted - 2003-08-03 : 14:42:35

I want the Price1 to override the Price2. This gives me a start but I'm not sure if it is exactly what I need; I guess I'll have to try it out. As an additional note, some customers get special pricing, which is what the Price1 is for. Would that change anything?

Thanks,
Kevin

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-03 : 14:52:59

Good point, Arnold. Actually, it all depends on the criteria.

I think we need some more information and a specific example or two would really help. imho, this is all WAY too general !

- Jeff

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-04 : 02:40:04

quote:
Originally posted by Mortifier

I want the Price1 to override the Price2. This gives me a start but I'm not sure if it is exactly what I need; I guess I'll have to try it out. As an additional note, some customers get special pricing, which is what the Price1 is for. Would that change anything?

Thanks,
Kevin

This is exactly what Arnold's query does. It gives first preference to Price1, but if Price1 is not available, it returns Price2. Of course, you will have to supply the appropriate conditions in the WHERE clauses. And let us know if this query actually performs better than the original.

Owais

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources