Geek Answers Handbook

MySQL data grouping

I have this table, let's call it first.

+----+---------+-----------------+
| id | link_id | url             |
+----+---------+-----------------+
|  1 |       1 | www.example.com |
|  2 |       1 | www.abc.com     |
|  3 |       1 | www.test.com    |
|  4 |       1 | www.t1.com      |
|  5 |       1 | www.newtest.com |
|  6 |       1 | www.testing.com |
|  7 |       1 | www.abc.com     |
|  8 |       1 | www.example.com |
|  9 |       1 | www.web1.com    |
| 10 |       1 | www.web2.com    |
| 11 |       2 | www.dear.com    |
| 12 |       2 | www.google.com  |
| 13 |       2 | www.flowers.com |
| 14 |       2 | www.yahoo.com   |
| 15 |       2 | www.abc.com     |
| 16 |       2 | www.dell.com    |
| 17 |       2 | www.web.com     |
| 18 |       2 | www.example.com |
| 19 |       2 | www.test.com    |
| 20 |       2 | www.abc.com     |
+----+---------+-----------------+
20 rows in set (0.00 sec)

Link_id sorts the primary identifier in the table. He tells me which URLs appear in link 1, link 2, etc.

What I want to do is: 1. Get all the unique URLs, 2. Show which links the URL belongs to.

Thus, the result will be:

+-----------------+---------+
| url             | link_id |
+-----------------+---------+
| www.example.com |       1 |
| www.example.com |       2 |
| www.abc.com     |       1 |
| www.abc.com     |       2 |
| www.test.com    |       1 |
| www.test.com    |       2 |
| www.t1.com      |       1 |
| www.newtest.com |       1 |
| www.testing.com |       1 |
| www.web1.com    |       1 |

... etc.

So, you can see that www.example.com appears twice because it is associated with two links 1 and 2, but web1.com appears only once since it belongs only to link 1.

I tried several different ones group by, but I only scratch my head even more.

Any help is appreciated. Here's a dump of the table if anyone needs it:

CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `link_id` tinyint(3) unsigned DEFAULT NULL,
  `url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=21 ;

INSERT INTO `table1` (`id`, `link_id`, `url`) VALUES
(1, 1, 'www.example.com'),
(2, 1, 'www.abc.com'),
(3, 1, 'www.test.com'),
(4, 1, 'www.t1.com'),
(5, 1, 'www.newtest.com'),
(6, 1, 'www.testing.com'),
(7, 1, 'www.abc.com'),
(8, 1, 'www.example.com'),
(9, 1, 'www.web1.com'),
(10, 1, 'www.web2.com'),
(11, 2, 'www.dear.com'),
(12, 2, 'www.google.com'),
(13, 2, 'www.flowers.com'),
(14, 2, 'www.yahoo.com'),
(15, 2, 'www.abc.com'),
(16, 2, 'www.dell.com'),
(17, 2, 'www.web.com'),
(18, 2, 'www.example.com'),
(19, 2, 'www.test.com'),
(20, 2, 'www.abc.com');
+5
7
SELECT url, GROUP_CONCAT(link_id) 
  FROM table1 
 GROUP 
    BY url;

URL-,

+1

DISTINCT ? ?

SELECT DISTINCT url, link_id
FROM `table1`
ORDER BY 1, 2
+3

If I don't understand the question, it looks like all you need is a DISTINCT clause:

select distinct url, link_id from table1;
+2
source 
Select url, link_id
From Table1
Group By url, link_id
+1
source 
select * from table group by link_id,url
+1
source

Well, you need to group both link_id and url, or maybe sort by URL so that the same URLs are together.

  SELECT url, link_id FROM table1
  ORDER BY url
  GROUP BY url, link_id
+1
source

If I am missing something:

SELECT DISTINCT url, link_id FROM table1;
+1
source

More articles:


All Articles