So,
1. Extract the Domain part of the URL. For example the domain of http://www.vexen.co.uk/countries/best.html is http://www.vexen.co.uk/:
SELECT Mid(URL_URL,1,InStr(8,URL_URL,"/")) AS URL_Domain FROM T_URLs;
2. I need to Group By and Order By this URL_Domain field, which is where my problems start. So I need extra SQL like:Count(URL_Domain)
andGROUP BY Mid(URL_URL,1,InStr(8,URL_URL,"/")) ORDER BY URL_Domain3. I then want to count how many URLs are in that domain. The result should be a list of URLs, with an extra "Domain_Q" field that counts how many other URLs are also in that domain:
In total:
SELECT Mid(URL_URL,1,InStr(8,URL_URL,"/")) AS Domain_URL, Count(URL_URL) AS Domain_Q
FROM T_URLs
GROUP BY Mid(URL_URL,1,InStr(8,URL_URL,"/")), URL_URL
ORDER BY URL_URL;
This will later let me iterate through every URL that exists in a domain which has more than 10 URLs in it.But the SQL so far returns a (correct) list of URL_URLs and Domain_URLs, but, the Domain_Q says "1" for every line in the results.
It works if I put the Count_Q in a different SQL query, but then I end up creating a circular reference when I try to bring the Domain_Q back into the URL list. Can anyone work out (a) what I'm talking about, and (b) what nifty little trick I'm missing to get this done sensibly?