MySQL group on url data strings that share the root domain

Question

The database is currently formatted as follows:

id (unique identifier)
url: http://domain.com/page.html

URLs are from the same domain and from different domains.

This also needs to be done in a single request so that I can use things like restrictions on displaying data to the end user.

Data

1, http://domain.com/about.html
2, http://domain.com/index.html
3, http://anotherdomain.com/index.html
4, http://anotherdomain.com/contact.html

Expected Result
(I just want to return the first URL (the data sorting order is ever ordered first, this example is an "ASC id") of each group, where the groups consist of URLs that share the root domain.)

1, http://domain.com/about.html
3, http://anotherdomain.com/index.html

Answer 1

,

SELECT REPLACE(REPLACE(SUBSTRING_INDEX(LOWER(table.url), '/',  3), 'www.', ''), 'http://', '') AS domain
FROM table
GROUP BY domain

Answer 2

:

SELECT T2.id, url FROM (
    SELECT MIN(id) AS id FROM Table1
    GROUP BY SUBSTRING_INDEX(url, '/', 3)
) AS T1
JOIN Table1 AS T2
ON T1.id = T2.id

:

1, 'http://domain.com/about.html'
3, 'http://anotherdomain.com/index.html'

Answer 3

If you did not mean the ORDER BY url, GROUP BY also works with functions such as substring ().