agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: left outer join to pull in most recent record
Date: Tue, 9 Oct 2018 14:18:01 +0100
Message-ID: <201810091418.01620.gary.stainburn@ringways.co.uk> (raw)

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 ...........

view thread (2+ messages)  latest in thread

Message-ID: <201810091418.01620.gary.stainburn@ringways.co.uk>
Permalink:  ../201810091418.01620.gary.stainburn@ringways.co.uk/
Also on:    postgresql.org/message-id/201810091418.01620.gary.stainburn@ringways.co.uk

reply

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: gary.stainburn@ringways.co.uk, pgsql-sql@lists.postgresql.org
  Subject: Re: left outer join to pull in most recent record
  In-Reply-To: <201810091418.01620.gary.stainburn@ringways.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox