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-23 09:46:02 |
Message-ID: | CAFj8pRBAR5gNpuQAsCaQmGP=qy2JNqcJ-ZUPrWRUmE99WfNjhg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gary Stainburn | 2012-05-23 09:47:27 | Re: left outer join only select newest record |
Previous Message | Oliveiros d'Azevedo Cristina | 2012-05-23 09:37:31 | Re: left outer join only select newest record |