Thread: left outer join to pull in most recent record

I have the following stock list table.

# select * from stock;
 s_stock_no | s_regno 
------------+---------
 N12345     | GPS1
 N12346     | TEST1
 U123       | GPS1
(3 rows)

This shows two vehicles. 
GPS1 was originally a new vehicle which we then sold.  
TEST1 is also a new vehicle
GPS1 is sunsequently purchased back from the customer as a part exchange, and 
is put on the stock table as a used car.

If I later want to do a search based on the registration number, wishing to 
retrieve the most recent record what is the *best* (quickest or least CPU 
time depending on your preference) way to do this?

By most recent record, I mean the record with the highest stock number.

I'm looking for a solution to do a straight select where.....
but I am also looking for a solution that can be used in a left join, for 
example

select diary.*, stock.* 
    from diary
    left outer join stock on ...........

select distinct s_regno, max(s_stock_no) over (partition by s_regno order by s_regno) from stock;

I have the following stock list table.

# select * from stock;s_stock_no | s_regno 
------------+---------N12345     | GPS1N12346     | TEST1U123       | GPS1
(3 rows)

This shows two vehicles. 
GPS1 was originally a new vehicle which we then sold.  
TEST1 is also a new vehicle
GPS1 is sunsequently purchased back from the customer as a part exchange, and 
is put on the stock table as a used car.

If I later want to do a search based on the registration number, wishing to 
retrieve the most recent record what is the *best* (quickest or least CPU 
time depending on your preference) way to do this?

By most recent record, I mean the record with the highest stock number.

I'm looking for a solution to do a straight select where.....
but I am also looking for a solution that can be used in a left join, for 
example

select diary.*, stock.* from diaryleft outer join stock on ...........

-- 

Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer