CODEWITHSUNDEEP
mysqlsql
zenpoy
zenpoy

Reputation: 20136

SQL: JOIN tables with the result having unique values on one of the columns?

I would like to join two tables but to specify that the result should have unique values on one of the columns.

http://sqlfiddle.com/#!2/70ded/4

Instead of 

URLID DOMAINID
13    5
9     3
10    3
11    4
12    4
6     2
7     2
8     2
1     1
2     1
3     1
4     1
5     1

I would like to get:

URLID DOMAINID
13    5
9     3
11    4
6     2
1     1

Is it possible doing it and by that creating a faster query?

Upvotes: 1

Views: 129

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Your query will not be faster, because you have to remove the duplicates. Here is a MySQL way:

select urlID, domainID
from (<your query>) as t
group by domainID

This uses a MySQL mis(feature) called hidden columns. In most databases, you would use:

select min(urlID), domainID
from (<your query>) as t
group by domainID

Upvotes: 1

Francis P
Francis P

Reputation: 13665

SELECT urlid,domain_stack.domainid
FROM  domain_stack
INNER JOIN url_stack
ON url_stack.domainid = domain_stack.domainid
GROUP BY domainid

Upvotes: 0

Taryn
Taryn

Reputation: 247850

How about this? I just applied an aggregate to the urlid and added a group by:

SELECT min(urlid) urlid, domain_stack.domainid 
FROM  domain_stack
INNER JOIN url_stack
  ON url_stack.domainid = domain_stack.domainid
group by domain_stack.domainid 
order by domain_stack.domainid desc

See SQL Fiddle with Demo

Upvotes: 1

Related Questions