CODEWITHSUNDEEP
mysqlsql
Pradeep Behera
Pradeep Behera

Reputation: 513

Show unique values of a query result that uses UNION ( union returns two columns, I want to show unique values from one column)

I have two tables shared_news and saved_news , both of them have link, title and timestamp column. I want to combine link and title of both the table and filter unique values based on link column and ORDER BY timestamp. I need both link and title for showing in my app.

When I use ,

SELECT link, title, timestamp FROM saved_news UNION SELECT link, 
title, timestamp FROM shared_news ORDER BY timestamp DESC

I am getting duplicate values. I tried many posts in stackoverflow and elsewhere in internet but could not do it. I need your help please.

Here is a screen shot on what is now and what I'm expecting.

http://snpy.in/d2OiQO

Upvotes: 0

Views: 1292

Answers (4)

nbk
nbk

Reputation: 49373

Try this please eventually you have Spaces leading or trailing

SELECT TRIM(link), TRIM(title), TRIM(timestamp) timestamp FROM saved_news 
 UNION DISTINCT 
SELECT TRIM(link), TRIM(title), TRIM(timestamp) timestamp FROM shared_news 
ORDER BY timestamp DESC;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If the duplicate values are only between tables rather than within a table, then the most efficient method is probably:

select san.link, san.title, san.timestamp 
from saved_news san
union all
select shn.link, shn.title, shn.timestamp 
from shared_news shn
where not exists (select 1
                  from saved_news san
                  where san.link = shn.link
                 );

And you want an index on saved_news(link).

This should be much faster than methods that use union, select distinct, or group by.

Upvotes: 1

forpas
forpas

Reputation: 164069

It seems like you are interested only in the column link to be unique.
So after the UNION use group by link and aggregate the other columns:

select t.link, max(t.title) title, max(t.timestamp) timestamp
from (
  select link, title, timestamp from saved_news 
  union 
  select link, title, timestamp from shared_news 
) t
group by t.link
order by timestamp desc

Upvotes: 1

Gavin
Gavin

Reputation: 2365

I'm not entirely sure about the goal but this should get you all link & titles with no duplicates

SELECT DISTINCT a.link, a.title FROM (
    SELECT link, title, timestamp FROM saved_news ORDER BY timestamp DESC UNION 
    SELECT link, title, timestamp FROM shared_news ORDER BY timestamp DESC
) as a;

or if you need them with timestamps, but only from the table's row in which it first appears:

SELECT a.link, a.title, a.timestamp FROM (
    SELECT link, title, timestamp FROM saved_news ORDER BY timestamp DESC UNION 
    SELECT link, title, timestamp FROM shared_news ORDER BY timestamp DESC
) as a GROUP BY a.link, a.title;

not 100% on the placement of that order by.

Upvotes: 0

Related Questions