I made a past and cut copy error
the correct answer is:

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 ranking

regards
PAscal


--- In firebird-support@yahoogroups.com, "devincayce" <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 !
> > >
> >
>