Re: Problem using Subselect results

I did try the following:

SELECT DISTINCT ON (two.two_id) two.two_value FROM one,two WHERE 
(one.two_id=two.two_id 
AND one.updatenr > two.updatenr) ORDER BY two.updatenr ASC;

I thought this would
a) order the result list by updatenr
b) return the first record only for records that are equal on the two.two_id 
field
which would return exactly what I need - return the record with the  

But what I get instead is "ERROR:  SELECT DISTINCT ON expressions must match 
initial ORDER BY expressions" *grrrmpfh*

But after a while playing around I noticed that the "initial" in that sentence 
above is important - adding two.two_id as first argument to the ORDER BY does 
not change the result but fulfill the requirements - the expressions don't have 
to be excactly the same - just the initial argument.

My view which returns the (hopefully) correct results:

SELECT DISTINCT ON (two.two_id) two.two_value FROM one, two WHERE ((one.two_id 
= two.two_id) AND (one.updatenr > two.updatenr)) ORDER BY two.two_id, 
two.updatenr;

Next step is to include corresponding information from table three - now I need 
subselects right? no way around with this DISTINCT and ORDER by stuff in it ...


Many thanks for your help so far,
Oliver



Quoting Oliver Heinz <oheinz@stud.fbi.fh-darmstadt.de>:

> I'll try this tomorrow - combining DISTINCT ON (two.two_id) and sorting by
> two.updatenr could (should) have the desired effect - I never thought about
> using ORDER and DISTINCT that way.
> 
> I'll report my success or failure...
> 
> Thanks so far!
> 
> Bye,
> Oliver


-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/