469,625 Members | 1,094 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

search engine in SQL server ?

hi,

I'm building a simple search engine using keyword and title of a page:

select ID, content_ID, 3 as 'weight'
from tblPage
where keywords LIKE '%test%'
union
select ID, content_ID, 2
from tblPage
where title LIKE '%test%'

now I want to sum and order 'weight'
thx

Jul 23 '05 #1
3 1473
rapataa (dg@rapataa.frup) writes:
I'm building a simple search engine using keyword and title of a page:

select ID, content_ID, 3 as 'weight'
from tblPage
where keywords LIKE '%test%'
union
select ID, content_ID, 2
from tblPage
where title LIKE '%test%'

now I want to sum and order 'weight'


It's not exactly clear to me what you mean by sum and order. You
can always add an "ORDER BY weight" at the end of the query. Yes,
you can even do:

SELECT ID, content_ID, weight
FROM (select ID, content_ID, 3 as 'weight'
from tblPage
where keywords LIKE '%test%'
union
select ID, content_ID, 2
from tblPage
where title LIKE '%test%') x
ORDER BY weight
COMPUTE SUM(weight)

But the COMPUTE clause is non-relational, and use of it is deprecated.

I suggest that you post:

o CREATE TABLE statement your table.
o INSERT statements with sample data.
o The desired result from the sample data.

This can make it a little clearer what you are looking for.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
This slight amendment to Erland's query should do the job you are looking
for.
Its untested as there were no create/insert scripts posted with the original
query.

SELECT ID, content_ID, Sum(weight)
FROM (select ID, content_ID, 3 as 'weight'
from tblPage
where keywords LIKE '%test%'
union all
select ID, content_ID, 2
from tblPage
where title LIKE '%test%') x
GROUP BY ID, content_ID
ORDER BY Sum(weight)

'Union' was changed to 'Union all' to prevent an unnecessary distinct sort
in the execution plan.

Mr Tea

"rapataa" <dg@rapataa.frup> wrote in message
news:59**************************@msgid.xenosite.n et...
hi,

I'm building a simple search engine using keyword and title of a page:

select ID, content_ID, 3 as 'weight'
from tblPage
where keywords LIKE '%test%'
union
select ID, content_ID, 2
from tblPage
where title LIKE '%test%'

now I want to sum and order 'weight'
thx


Jul 23 '05 #3
following code works:

----
Select ID, content_ID, sum(weight) weight
from
(
select ID, content_ID, 3 as 'weight'
from tblPage
where keywords LIKE '%test%'

union

select ID, content_ID, 2 as 'weight'
from tblPage
where title LIKE '%test%'
)
faketablename
group by ID, content_ID
order by weight desc
-----

it returns a list ikt this:

ID, content_ID, weight
275 | 250 | 6
273 | 254 | 3
276 | 251 | 2
etc, etc
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Frank | last post: by
2 posts views Thread by Benny Alexander | last post: by
2 posts views Thread by Dhanraj K.S | last post: by
3 posts views Thread by Carol | last post: by
5 posts views Thread by George | last post: by
2 posts views Thread by vichet | last post: by
83 posts views Thread by D. Dante Lorenso | last post: by
reply views Thread by Mark Goldin | last post: by
3 posts views Thread by hazly | last post: by
3 posts views Thread by darrel | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.