From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: left outer join only select newest record |
Date: | 2012-05-24 08:17:00 |
Message-ID: | CAFj8pRACnUVG_0UgxN+BKfDAhdW2KVntNOgFDtoPjD5u623=aQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
>
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
From | Date | Subject | |
---|---|---|---|
Next Message | Gary Stainburn | 2012-05-24 08:21:43 | Re: left outer join only select newest record |
Previous Message | Samuel Gendler | 2012-05-24 07:07:18 | Re: sub query and AS |