Re: WHERE on an alias

----- Original Message -----
From: "Joseph Shraibman" <jks(at)selectacast(dot)net>
Subject: Re: [SQL] WHERE on an alias

> If I try to put a distinct on in my subselect int the from I get:
>
> ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY
expressions
>
> what does that mean?

You need to (at least) ORDER BY the expression you want to have the
DISTINCT ON.

Tom's example would work fine:

> > select distinct on (ml.f1,ml.f2) * from ut,ml
> > where ut.f1 = ml.f1 and ut.f2 = ml.f2
> > order by ml.f1, ml.f2, ml.f3 desc;

Wrong:

select distinct on (ml.f1,ml.f2) * from ut,ml
where ut.f1 = ml.f1 and ut.f2 = ml.f2
order by ml.f2, ml.f1, ml.f3 desc;

Andre