That won't work since '2' refers to column position and you just select
one column. So you're stuck with either using NOT CONTAINING in the
second select or using CASE with no need for any union at all.

I by far prefer the CASE statement over UNION.

Set

-Norway may be a distant country from most of the world, but is that the
reason for the minimum 30 minutes delay for my messages to reach
yahoogroups?

devincayce wrote:

> Thanks for replying and your suggestion seems like it should work in concept, but I can't
> make it work in practice.
>
> Should the ORDER BY 2 be inside or outside the parantheses? When its outside I get an
> error because I think its not find the #2 field in the Select statement and then when I put it
> inside the parathesis it might be sorting the inner select properly, but then the outer
> select just displays the records in the record order and NOT the order dictated by the
> ORDER BY command.
>> To complete the proposals,
>> if you are ready to use Firebird 2.0 you could solve your problem as
>> follow:
>>
>> Select DISTINCT title from (
>> SELECT DISTINCT title, 1 AS ranking WHERE title CONTAINING "Jet
>> Ranger" UNION SELECT DISTINCT title, 2 AS ranking WHERE title
>> CONTAINING "Jet" OR title CONTAINING "Ranger")
>> ORDER BY 2
>>
>> Regards
>> PAscal
>>
>> --- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
>> <svein.erling.tysvaer@> wrote:
>>> You're right, Alexandre. And if it is a reasonably updated Firebird
>>> version (CASE exists in Firebird 1.5, it may be part of Firebird 1.0),
>>> there's not even a need for any UNION:
>>>
>>> SELECT DISTINCT title,
>>> case when title CONTAINING 'Jet Ranger' then 1 else 2 end as OrderField
>>> FROM UnknownTable
>>> WHERE title CONTAINING 'Jet' OR title CONTAINING 'Ranger'
>>> ORDER BY 2
>>>
>>> Set
>>>
>>> Alexandre Benson Smith wrote:
>>>> devincayce wrote:
>>>>> I am trying to create a custom search text query so I can have
>> control over how the ranking is
>>>>> assigned and for other reasons. This is a simple example:
>>>>>
>>>>> SELECT DISTINCT title, 1 AS ranking WHERE title CONTAINING "Jet
>> Ranger" UNION
>>>>> SELECT DISTINCT title, 2 AS ranking WHERE title CONTAINING "Jet"
>> OR title CONTAINING
>>>>> Ranger" ORDER BY 2
>>>>>
>>>>> which works. However, I get multiple versions of the same record
>> because it will return the
>>>>> record titled "Jet Ranger" twice with a rank of 1 and 2.
>>>>>
>>>>> Is there a way to insure it only returns the "Jet Ranger" record
>> once? Is there a way to "hide"
>>>>> the rank column from the UNION function when it removes
>> duplicates? Or is there a better
>>>>> way to do all of this? Thanks.
>>>>>
>>>> Hi !
>>>>
>>>> I think there is a bunch of ways of achieving this, one simples
>> will be:
>>>> SELECT DISTINCT title, 1 AS ranking WHERE title CONTAINING 'Jet
>> Ranger' UNION
>>>> SELECT DISTINCT title, 2 AS ranking WHERE (title CONTAINING 'Jet'
>> OR title CONTAINING
>>>> 'Ranger') and (title not CONTAINING 'Jet Ranger') ORDER BY 2
>>>>
>>>> see you !