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.
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
