--- In firebird-support@yahoogroups.com, "legrand_legrand_63"
<grand.brun.63@...> wrote:

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

You are using derived tables, a feature new to Firebird 2 which is not
yet released, and such a query would not work on Firebird 1.5. Help on
this sort of thing is aa bit OT. But, in essence, your query looks
like this:

Select DISTINCT title
from SomeDerivedTable
ORDER BY ranking

As you could potentially have two records with identical title fields
but with different rankings, this is obviously an impossible ask.

Maybe your close bracket is in the wrong place, but I am not sure
whether order by works in a derived table - consult the release notes
for Firebird 2. Of course, if you had meant your order by in the
derived table, it is a bit useless because you have a distinct
operator in your outer select, so it will in the absense of an order
by field sort on the title (as an implementation artifact).

Do not rely on it being sorted by title just because you have a
distinct, if you expect it ordered, make sure it is included in an
order by clause. In the absense of an order by field, the SQL standard
allows for the results to be returned in any convenient order for the
DBMS.

Maybe you could use something like:

Select title, min(Ranking)
from SomeDerivedTable
GROUP BY title
ORDER BY 2, title

If you just wanted to use the smallest ranking that was assigned to
the title.

Adam