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?
Tom Lane wrote:
> Joseph Shraibman <[EMAIL PROTECTED]> writes:
>
>>Basically there is the utable, which has two keys in it. I want to
>>select some rows from the utable. Some columns come from the dtable,
>>which has a key that matches to one of the utable keys, and I want to
>>select the last value from the mltable which has the same two keys as
>>utable and it has a serial value that I use do determine which is the
>>last entry. I also want to use the last value from the mltable in the
>>WHERE clause.
>>
>
> I'm thinking you could use a subselect along the lines of
>
> 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;
>
> f1 and f2 being the match keys and f3 being the serial value.
> Given two-key indexes, I get plans along the lines of
>
> Unique (cost=109.96..110.08 rows=2 width=20)
> -> Sort (cost=109.96..109.96 rows=25 width=20)
> -> Merge Join (cost=0.00..109.38 rows=25 width=20)
> -> Index Scan using ut_pkey on ut (cost=0.00..52.00 rows=1000
>width=8)
> -> Index Scan using ml_f1f2 on ml (cost=0.00..52.00 rows=1000
>width=12)
>
> which doesn't look too bad.
>
> regards, tom lane
>
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
