Prevent UNION from returning duplicate RESULTS

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 2012 Forums

Transact-SQL (2012)

Prevent UNION from returning duplicate RESULTS

Author

Topic

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2015-02-06 : 07:41:44

I am doing a union on 2 sql statements. when the result returns i notice that i have duplicate records based upon the expire_date
basicaly they both have the same id's but different expration dates
id exp_date
3435363 2016-01-21-00:00:00
3435363 2018-21-00:00:00

how do i ensure that my union only brings back the max date of both result example

id exp_date
3435363 2018-01-21-00:00:00

instead of
id exp_date
3435363 2018-1-21-00:00:00

3435363 2016-01-21-00:00:00

jleitao
Posting Yak Master

100 Posts

Posted - 2015-02-06 : 08:03:28

your SQL command just use that 2 columns?

------------------------
PS - Sorry my bad english

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2015-02-06 : 08:24:06

It has other columns but I only want to return max expiration date if it has more than one
When the union executes. That way only one result will return for records with multiple expiration dates

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-06 : 08:34:07

select id, max(exp_date)
from (
select id, exp_date from table1
union all
select id, exp_date from table2
) u
group by id

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2015-02-06 : 10:27:42

I still get both rows returned when i just wanted the max record with expdate, when i add my additional columns and add them to the group by

select distinct id, max(ExpDate) as ExpDate,max(term_date) as T_date,Company,GroupID
--select id, exp_date from table1
--union all
--select id, exp_date from table2
--) u
group by id, Company, GroupID

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-06 : 11:25:45

time to post some sample data showing all columns. Please post as INSERT INTO statements

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-02-06 : 11:38:14

[code]
WITH AllResults
AS
(
SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2
)
,OrderedResults
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY exp_date DESC) AS rn
FROM AllResults
)
SELECT *
FROM OrderedResults
WHERE rn = 1;
[/code]

jleitao
Posting Yak Master

100 Posts

Posted - 2015-02-06 : 12:01:34

i asked you about the number of columns...

However, Ifor query will solve your problem.

Just one appointment, if you use a "group by" you don't need the "distinct"

------------------------
PS - Sorry my bad english

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2015-02-07 : 13:42:33

using this example did work
Posted - 02/06/2015 : 11:38:14
--------------------------------------------------------------------------------

WITH AllResults
AS
(
SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2
)
,OrderedResults
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY exp_date DESC) AS rn
FROM AllResults
)
SELECT *
FROM OrderedResults
WHERE rn = 1;

HOWEVER IT IS TAKING A LONG TIME TO RETURN MY RESULTS, If i do a top one on the view using the above it takes almost a minute. is there a way to optimize this to return faster results. I tried to create a table then insert the results from the query in the table however its giving syntax errors. how can insert this reslut into a table.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2015-02-08 : 00:40:35

There's a lot of ways to re-write this query for better results, but the better ways are going to take understanding your real data.

Method 1


WITH AllResults
AS
(
SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2
)
,OrderedResults
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY exp_date DESC) AS rn
FROM AllResults
)
SELECT *
FROM OrderedResults
WHERE rn = 1;

Method 2


SELECT * 
into #tmp
FROM Query1
UNION ALL
SELECT * FROM Query2

select a.*
from #tmp a
inner join
(select id,max(exp_date) as exp_date
from #tmp aa
group by id
) b
on a.id = b.id

Method 3


SELECT * 
into #tmp
FROM Query1
UNION ALL
SELECT * FROM Query2

select *
from
(
select a.*,row_number() over (partition by id order by exp_date desc) as rowid
from #tmp aa
) a
where a.rowid  =1

Method 4


SELECT * 
into #tmp
FROM Query1
UNION ALL
SELECT * FROM Query2

select * from #tmp a
cross apply
(select top 1 id from #tmp aa where aa.id = a.id order by exp_date desc) b

Some more to check as well, your indexes might not be up to par either.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources