From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: left outer join only select newest record
Date: Wed, 23 May 2012 11:33:21 +0100
Message-ID: <201205231133.21123.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <CAFj8pRBAR5gNpuQAsCaQmGP=qy2JNqcJ-ZUPrWRUmE99WfNjhg@mail.gmail.com>
References: <201205231027.39198.gary.stainburn@ringways.co.uk>
<CAFj8pRBAR5gNpuQAsCaQmGP=qy2JNqcJ-ZUPrWRUmE99WfNjhg@mail.gmail.com>
On Wednesday 23 May 2012 10:46:02 Pavel Stehule wrote:
> select distinct on (s.s_registration) *
> ... order by u.ud_id desc
I tried doing this but it complained about the order by.
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 used_diary u
on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470' order by
s_stock_no, ud_id desc;
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
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 used_diary u
on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470' order by
ud_id desc;
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
goole=#
>
> or
>
> select *
> from stock_details s
> left join (select * from used_diary where (ud_id,
> ud_registration) = (select max(ud_id), ud_registration from used_diary
> group by ud_registration)) x
> on s.s_registration = x.ud_registration;
>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: left outer join only select newest record
In-Reply-To: <201205231133.21123.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox