how to get 1 result of a url list with same domain

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to get 1 result of a url list with same domain

Author

Topic

BennoM1984
Starting Member

4 Posts

Posted - 2011-11-11 : 07:41:56

Hi

i have a database with the following format:

Table: URL_LIST
Format: Id,url
content:
1 | http://www.google.com/nice_page
2 | http://www.google.com/good/
3 | http://www.google.com/bad/
4 | http://www.google.com/well/
5 | http://www.yahoo.com/test/
6 | http://www.yahoo.com/more/test/
7 | http://www.yahoo.com/example/
8 | http://www.bing.com/example.html
9 | http://www.bing.com/example.php?variable=34234&hit="no"

my first question is how can i get just one url of each domain?
second: how can i get a random url out of each domain?

so my result set should looks like:
2 | http://www.google.com/good/
5 | http://www.yahoo.com/test/
9 | http://www.bing.com/example.php?variable=34234&hit="no"

Thank You

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 07:54:50

for the domain
select domain = left(url,charindex('/',url) from urllist

to get one of each
select* from (
select domain = left(url,charindex('/',url), url, ROW_NUMBER() over (psartition by left(url,charindex('/',url), order by newid())from urllist
) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

eralper
Yak Posting Veteran

66 Posts

Posted - 2011-11-11 : 08:09:01

Hello Benno,
Please try the following SELECT statement
Actually it has the same structure with nigel's solution
But I'm using a udf for extracting domain from URL
You can find the function here :
[url]http://www.kodyaz.com/articles/how_to_parse_url_for_domain_name.aspx[/url]

with cte as (
	select 
		url, 
		dbo.Parse_For_Domain_Name(url) domain
	from URL_LIST 
)
select
	url
from (
	select
		row_number() over (partition by domain order by newid()) rn,
		url
	from cte
) t
where rn = 1

I hope it helps

-------------
Eralper
http://www.kodyaz.com

BennoM1984
Starting Member

4 Posts

Posted - 2011-11-11 : 08:14:33

wow, fast reply, thx

but i got the following error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' url, ROW_NUMBER() over (psartition by left(url,charindex('/',url), order by new' at line 2

BennoM1984
Starting Member

4 Posts

Posted - 2011-11-11 : 08:17:56

@ eralper

thanx for fast reply, but i like to use the query from php, so i dont know how to implement sql functions...it would be easier for me to use a select statement...
thanx

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 08:27:14

Try a MySQL site rather than a SQL Server site?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BennoM1984
Starting Member

4 Posts

Posted - 2011-11-11 : 08:32:42

user knowledge is better here i think..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources