SQL 2005 Grouping results

[ntqb]

Hi all!

I have a table named Pages and two(2) columns.

This are the two columns named URL and PAGE
---------------------------------------------------------------
URL ¦ PAGE
[website1.com...] ¦ [website1.com...]
[website1.com...] ¦ [website1.com...]
[website1.com...] ¦ [website1.com...]
[website2.com...] ¦ [website2.com...]
[website2.com...] ¦ [website2.com...]
---------------------------------------------------------------

When i search the database i want to show one result per website.

I used DISTINCT and GROUP BY but is not working for me. I dont know what to do.

Maybe somebody can help me with this!

Im using ASP and SQL 2005 with Full-Text Search

Thanks!

[LifeinAsia]

SELECT DISTINCT URL
FROM Table

[Demaestro]

normalizing data question.. why is the URL stored in the page column when it is already in the URL column?

[ntqb]

for comparing them... if you have an ideea let me know. thanks!

i used also distinct but is not working for me.

when i try SELECT DISTINCT URL, Page, Title, ... the DISTINCT show me just the unique URL and not one result per URL

[ntqb]

i used:
SELECT DISTINCT URL, Page, Title from Pages where FREETEXT(Title, '"& request.querystring("q") &"')

Its not working

I have duplicates results

[Demaestro]

Well the reason is there is more then one Page per website so when you ask it to select Page it has no way of showing the the URL once but all the different pages.

This statement
SELECT DISTINCT URL, Page, Title from Pages where FREETEXT(Title, '"& request.querystring("q") &"')

This is asking for a distinct url + title + page so if they aren't all unique then it will create a new row for each unique combination of url + title + page

URL ¦ PAGE
[website1.com...] ¦ [website1.com...]
[website1.com...] ¦ [website1.com...]

The combination of URL and Page aren't unique so it has to show them both.

[ntqb]

You have an ideea how to fix this ?

[LifeinAsia]

Perhaps you should backup and let us know exactly what you are trying to accomplish. You say you want to show 1 row for each URL, but you aren't defining how to choose which PAGE to use for each URL.

[ntqb]

The PAGE is show when somebody search for a specific word...

[ntqb]

i want to show the results just like GOOGLE Search or YAHOO search

[Demaestro]

All those search engines you mention display more then one result per website as well so I don't know why you would only want to show each site only once.

[Demaestro]

To keep on the question though

SELECT DISTINCT URL from Pages where FREETEXT(Title, '"& request.querystring("q") &"')

[ntqb]

because i dont have a lot of websites in my database and when someone search for something the webpage results are 400 and the websites urls are 5-6. I can use ORDER BY NEWID() but the results from the same site are show in the next page(the page 2)

[LifeinAsia]

So again, for each URL, how do you determine which PAGE is displayed? Until you decide that, you can't write the query.

[ntqb]

how about GROUP BY and HAVING COUNT ?
any ideea to group results ?

i used GROUP BY URL HAVING COUNT(*)=1 or HAVING COUNT(URL)=1 or HAVING COUNT(URL)>1 but nothing working

[syber]

If you only want to show one page to show per website, you could do something like this:

SELECT * FROM 
(SELECT URL, Page, Title  
 FROM Pages  
 WHERE FREETEXT(Title, '"& request.querystring("q") &"') ) AS p1 
WHERE Page = (SELECT MAX(Page) FROM Pages  
        WHERE URL = p1.URL) 

[ntqb]

ooooooooooooo yeah :)
Thats work perfect!
This is what i was looking for.
Thanks to all!

[ntqb]

Back again...

When i use a database with 20.000 rows all works perfect
but
when i use a database with 1.200.000 rows i have an timeout after 30 seconds and no results

The error message is:
Microsoft SQL Native Client error '80040e31'
Query timeout expired

[ZydoSEO]

Have you used SQL Server Management Studio to look at the query execution plan? My guess is you have an indexing problem (lack of proper indexes).

[ntqb]

when i use
select * from Pages where FREETEXT(Title, 'word')

the results are show fast and when i use

SELECT * FROM (SELECT URL, Page, Title FROM Pages WHERE FREETEXT(Title, '"& request.querystring("q") &"') ) AS p1 WHERE Page = (SELECT MAX(Page) FROM Pages WHERE URL = p1.URL)

the query takes up to 10 minutes

[LifeinAsia]

Make sure you have your table indexed properly. If you frequently add/remove data, it's also a good idea to rebuild your indexes periodically.

[ntqb]

i was rebuild the indexes yesterday but the things are the same