Re: left outer join only select newest record - Mailing list pgsql-sql

From Thomas Kellerer
Subject Re: left outer join only select newest record
Date
Msg-id jpidmq$dec$1@dough.gmane.org
Whole thread Raw
In response to Re: left outer join only select newest record  (Gary Stainburn <gary.stainburn@ringways.co.uk>)
List pgsql-sql
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
leftouter 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.



pgsql-sql by date:

Previous
From: Gary Stainburn
Date:
Subject: Re: left outer join only select newest record
Next
From: "Oliveiros d'Azevedo Cristina"
Date:
Subject: Re: left outer join only select newest record