473,324 Members | 2,268 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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 1555
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Frank | last post by:
Hello, I'm running a site with +20.000 articles. The articles (html files) are saved on the server as txt files. Alle other data (author, date, category and so on) are in a MySQL db. Before we...
2
by: Benny Alexander | last post by:
Hi, We are developing a CMS in ASP and before we write the code for a complete site search script and Forum, we want to check if is there any good ones? Kindly please let us know. Thanks in...
2
by: Dhanraj K.S | last post by:
Hi All, Is there any Site search application which is extremely powerful. It would be a great help if some one can suggest! Thanks in advance,
3
by: Carol | last post by:
What's the best way to do a site search of my site? I am using asp and access heavily on a regular ISP google? Or write a bunch of queries? what else is there?
5
by: George | last post by:
Hi, Anyone has the background for explaining? I have made a search on my name and I have got a link to another search engine. The link's title was the search phrase for the other search engine...
2
by: vichet | last post by:
Hi All; Please help me with some problem i want VBSCRIPT to search something in only my own website give me code thank vichet
83
by: D. Dante Lorenso | last post by:
Trying to use the 'search' in the docs section of PostgreSQL.org is extremely SLOW. Considering this is a website for a database and databases are supposed to be good for indexing content, I'd...
0
by: Mark Goldin | last post by:
I am working on a search engine. An aspx page shows some data. The user will type something in and click a button to search. I want to create a server solution for the search engine. So the search...
3
by: hazly | last post by:
I'm very new in the web technology and need advice on search engine. I want to develop a portal using PHP and MySQL on Linux. Need to know on the following features : 1. search engine that could...
3
by: darrel | last post by:
I'm in need of a cheap, windows-based web site indexer/search engine that, ideally, has some .net integration and/or can sit along side of an asp.net web site fairly easily. We've used DTSearch...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.