Advanced SQL

[Skinner]

Hypothetically, suppose such a zone file of .me|net|org|co|ltd|plc.uk existed, and just for ease, this file was setout

domain, extension, created

for example data

alpha, org, 1997-01-01
alpha, co, 1995-01-01
beta, co, 1999-01-01
beta, me, 2003-01-01

Supposed I wanted select the oldest domain by created grouped by keyword, any clue how I'd run such a query ?

Its a little (read massively) more advanced than my skill set.

 

[RobM]

Well in the example you've given with just 3 fields you could run:
SELECT * FROM table ORDER BY created ASC LIMIT 0,1;

 

[martin-s]

Something like this..

SELECT keyword, ext, created from domain d1
INNER JOIN (
SELECT keyword, MIN(created) AS created
FROM domain
GROUP BY keyword
) AS d2 ON d2.keyword=d1.keyword AND d2.created=d1.created
ORDER BY keyword, ext, created

 

[Skinner]

I've been trying really complex stuff like Martin-S did, even been trying functions, nothing seems to work.

RobM, that's returning only the oldest entry in the database, I want to return the oldest for each keyword/domain, there can be up to 6 ext per keyword, for example star has co/org/ltd/me/net, so I want sql return just the 0 as its the oldest.

0 [ext] => .co.uk
[created] => 1994-11-19
[domain] => star.co.uk

1 [ext] => .net.uk
[created] => 1996-10-17
[domain] => star.net.uk

2 [ext] => .ltd.uk
[created] => 1999-12-08
[domain] => star.ltd.uk

3 [ext] => .me.uk
[created] => 2008-08-04
[domain] => star.me.uk

4 [ext] => .org.uk
[created] => 2008-08-04
[domain] => star.org.uk

Pulling the data out of the database, and sort the array is easy but killing resources on large scale ops.

I tried the above Martin and I get "#1052 - Column 'keyword' in field list is ambiguous"

 

[martin-s]

My bad. Should have been:

SELECT d1.keyword, ext, d1.created from domain d1
INNER JOIN (
SELECT keyword, MIN(created) AS created
FROM domain
GROUP BY keyword
) AS d2 ON d2.keyword=d1.keyword AND d2.created=d1.created
ORDER BY d1.keyword, ext, d1.created

 

[Skinner]

Thanks Martin, Ill run it on my sample data, when I get back to pc.

Hopefully ill get some useful info back from this.

 

[Skinner]

The query ran with a little tweaking, on my sample data I returned 3,500 .co.uk owners who lose to .org.uk.

A small section...

a1
aas
aci
acd
acorn (interesting to me has dnssec enabled on whois)
affiliate
affiliates
ainsley
airmax
alarm
alerts
alex
alm
also
als
alter
ampersand
amps
amy
and
anagram
andys
anne
antibiotics
anything
app
are
argon
army
articles
artists
ash
ate
audiobooks
australia
austria
author
automate
automatic
avg

There are lots more LLLs affected.

 

[foz]

The query ran with a little tweaking, on my sample data I returned 3,500 .co.uk owners who lose to .org.uk.

Terrible outcome for .co.uk owners.

 

[martin-s]

Next step should be to send an HTTP request and see what response code you get for each domain.

I'm not actually that surprised about the LLLs - given the number sitting with investors.

 

[Skinner]

Insanely bad outcome for some.

I tried to run this on the main database but it drained the server resources, and died around the letter H, I'd guess the number in my database will hit around 11,000, and I've not imported .me|net|ltd|plc.uk yet.

I'm going to add ISP Tags so I can sort probable domainers out then I'll send the http requests, see which resolve.

 

[RobM]

I've compiled a quick list of 51,000ish .orgs that are older than .co.uk at http://www.domainview.co.uk/oldorg.txt
Will run the me.uk next

 

[Skinner]

I'll scan down Rob's list as well, see if I can pick out some real word businesses.

I figure we need simple way of explaining to businesses how it will work, while I appreciate some will think I'm shooting myself in the foot making business aware of this, but I'm still hoping .uk can be stopped