-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE TABLE urls (
    id INTEGER NOT NULL,
    url VARCHAR(255),
    PRIMARY KEY(id)
);

INSERT INTO urls VALUES (1,'http://www.eksperten.dk/blahblah/hej.php');
INSERT INTO urls VALUES (2,'http://www.cnn.com/23423');
INSERT INTO urls VALUES (3,'http://www.abc.net/234/464/sfd/davs.asp');
INSERT INTO urls VALUES (4,'http://www.eksperten.dk/stuff/hurra.php');
INSERT INTO urls VALUES (5,'http://www.bbc.co.uk/sdfs');

SELECT * FROM urls;

SELECT SUBSTRING(url,1,LOCATE('/',url,LOCATE('//',url)+2)) FROM urls;

SELECT SUBSTRING(url,1,LOCATE('/',url,LOCATE('//',url)+2)),COUNT(*) AS n
FROM urls
GROUP BY SUBSTRING(url,1,LOCATE('/',url,LOCATE('//',url)+2))
ORDER BY n DESC
LIMIT 1;

DROP TABLE urls;
