Re: left outer join only select newest record

2012/5/23 Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>:
> Hi folks,
>
> I know I've seen posts like this before but Google isn't helping today.
>
> I have two tables, vehicle stock and tax requests. Each vehicle can be taxed
> more than once, but I only want to pull in the most recent tax request - the
> one with the highest ud_id.
>
> I have the following, which obviously returning multiple records which then
> appears that the same vehicle is in stock multiple times.  How can I make it
> so we only show each vehicle once, showing the most recent tax request
> details.
>
>
> select * from stock_details s
> left outer join used_diary u on s.s_registration = u.ud_registration;

select distinct on (s.s_registration) *
... order by u.ud_id desc

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;

Regards

Pavel Stehule

>
>
> --
> Gary Stainburn
> Group I.T. Manager
> Ringways Garages
> http://www.ringways.co.uk
>
> --
> 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