Re: SQL query

From: Tommy Hagström <tomhag_at_rsv.se>
Date: Thu, 4 Nov 1999 12:31:34 +0100
Message-ID: <Pine.HPX.4.05.9911041158090.10020-100000@u30040.rsv.rsv.se>

On Wed, 3 Nov 1999, swamy v a wrote:

> I am writing a search engine to search for few words in a
> field, it am trying to make it work like below:
>
> If the user wants to find the fields containing the words
> "HISTORY ASIA", it should display in the following order:
>
> i) All fields having the words HISTORY and ASIA should be
> displayed first.
> ii) All fields having the word HISTORY should be displayed
> next
> iii) All fields having the word ASIA should be displayed
> next.
>
> I am trying to the above query in a single statement with
> LIKE and OR clauses, but is not returning in the proper
> order.
>
> thanx,
> swamy
>
>
> * Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is Beautiful
>
>

Try this one:

   select text from(

         select 1 sort, field text
           from table
          where field like '%ASIA%'
            and field like '%HISTORY%'
         union
         select 2 sort, field text
           from table
          where field like '%ASIA%'
            and field not like '%HISTORY%'
         union
         select 3 sort, field text
           from table
          where field not like '%ASIA%'
            and field like '%HISTORY%'
         )

   order by sort, text

Tommy Hagström
Email: tomhag_at_rsv.se Received on Thu Nov 04 1999 - 05:31:34 CST