From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: left outer join only select newest record
Date: Wed, 23 May 2012 12:22:56 +0200
Message-ID: <jpidmq$dec$1@dough.gmane.org> (raw)
In-Reply-To: <201205231047.27372.gary.stainburn@ringways.co.uk>
References: <201205231027.39198.gary.stainburn@ringways.co.uk>
<53C6FD0FE48B43C0AD419648FDC8D943@marktestcr.marktest.pt>
<201205231047.27372.gary.stainburn@ringways.co.uk>
Gary Stainburn, 23.05.2012 11:47:
> Here is a select to show the problem. There is one stock record and two tax
> records. What I'm looking for is how I can return only the second tax record,
> the one with the highest ud_id
>
> select 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';
>
> s_stock_no | s_regno | s_vin | s_created |
> ud_id | ud_handover_date
> ------------+---------+-------------------+----------------------------+-------+------------------
> UL15470 | YG12*** | KNADN312LC6****** | 2012-05-21 09:15:31.569471 |
> 41892 | 2012-04-06
> UL15470 | YG12*** | KNADN312LC6****** | 2012-05-21 09:15:31.569471 |
> 42363 | 2012-05-16
> (2 rows)
>
Something like:
select *
from (
select s_stock_no,
s_regno
s_vin,
s_created,
ud_id,
ud_handover_date,
row_number() over (partition by s_stock_no order by ud_id desc) as rn
from stock s
left outer join used_diary u on s.s_regno = u.ud_pex_registration
where s_stock_no = 'UL15470'
) t
where rn = 1
The "partition by s_stock_no order" isn't really necessary as your where clause already limits that to a single stock_no.
But in case you change that statement to return more than one stock_no in the future it will be necessary.
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: spam_eater@gmx.net
Subject: Re: left outer join only select newest record
In-Reply-To: <jpidmq$dec$1@dough.gmane.org>
* 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