Re: left outer join only select newest record

>
> This was more like what I was thinking, but I still get an error, which I
> don't understand.  I have extracted the inner sub-select and it does only
> return one record per registration. (The extra criteria is just to ignore old
> or cancelled tax requests and doesn't affect the query)
>
> goole=# select distinct on (s.s_stock_no) s_stock_no, s_regno, s_vin,
> s_created, ud_id, ud_handover_date from stock s left outer join (select
> ud_id, ud_pex_registration, ud_handover_date from used_diary where (ud_id,
> ud_pex_registration) = (select max(ud_id), ud_pex_registration from
> used_diary where (ud_tab is null or ud_tab <> 999) and ud_created >
> CURRENT_DATE-'4 months'::interval group by ud_pex_registration)) udIn on
> s.s_regno = udIn.ud_pex_registration;
> ERROR:  more than one row returned by a subquery used as an expression

sure, I am sorry

please, WHERE (ud_id, ud_pex_registration) = (SELECT ...

replace by

WHERE (..) IN (SELECT ..

Regards

Pavel

>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql