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 !