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 !
>