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