20i Domains

Advanced SQL

Discussion in 'Scripts and Coding' started by Skinner, Jul 18, 2013.

Thread Status:
Not open for further replies.
  1. Skinner

    Skinner Well-Known Member

    Joined:
    Jul 2008
    Posts:
    4,660
    Likes Received:
    139
    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.
     
  2. Domain Forum

    Acorn Domains Elite Member

    Joined:
    1999
    Messages:
    Many
    Likes Received:
    Lots
    articles.co.uk
     
  3. RobM

    RobM Retired Member

    Joined:
    Mar 2012
    Posts:
    3,289
    Likes Received:
    470
    Well in the example you've given with just 3 fields you could run:
    SELECT * FROM table ORDER BY created ASC LIMIT 0,1;
     
  4. martin-s United Kingdom

    martin-s Well-Known Member

    Joined:
    Jul 2012
    Posts:
    3,395
    Likes Received:
    239
    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
     
    Last edited: Jul 18, 2013
  5. Skinner

    Skinner Well-Known Member

    Joined:
    Jul 2008
    Posts:
    4,660
    Likes Received:
    139
    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.

    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"
     
  6. martin-s United Kingdom

    martin-s Well-Known Member

    Joined:
    Jul 2012
    Posts:
    3,395
    Likes Received:
    239
    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
     
    • Like Like x 1
  7. Skinner

    Skinner Well-Known Member

    Joined:
    Jul 2008
    Posts:
    4,660
    Likes Received:
    139
    Thanks Martin, Ill run it on my sample data, when I get back to pc.

    Hopefully ill get some useful info back from this.
     
  8. Skinner

    Skinner Well-Known Member

    Joined:
    Jul 2008
    Posts:
    4,660
    Likes Received:
    139
    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.
     
  9. foz

    foz Well-Known Member Exclusive Member

    Joined:
    Oct 2006
    Posts:
    3,041
    Likes Received:
    35
    Terrible outcome for .co.uk owners.
     
  10. martin-s United Kingdom

    martin-s Well-Known Member

    Joined:
    Jul 2012
    Posts:
    3,395
    Likes Received:
    239
    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.
     
  11. Skinner

    Skinner Well-Known Member

    Joined:
    Jul 2008
    Posts:
    4,660
    Likes Received:
    139
    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.
     
  12. RobM

    RobM Retired Member

    Joined:
    Mar 2012
    Posts:
    3,289
    Likes Received:
    470
  13. Skinner

    Skinner Well-Known Member

    Joined:
    Jul 2008
    Posts:
    4,660
    Likes Received:
    139
    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 :)
     
Thread Status:
Not open for further replies.