Skip site navigation (1) Skip section navigation (2)

Re: left outer join only select newest record

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: left outer join only select newest record
Date: 2012-05-23 10:33:21
Message-ID: 201205231133.21123.gary.stainburn@ringways.co.uk (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

Responses

pgsql-sql by date

Next:From: Oliveiros d'Azevedo CristinaDate: 2012-05-23 10:41:04
Subject: Re: left outer join only select newest record
Previous:From: Oliveiros d'Azevedo CristinaDate: 2012-05-23 10:29:27
Subject: Re: left outer join only select newest record

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group