Subject: Re: left outer join to pull in most recent record
To: pgsql-sql@lists.postgresql.org
References: <201810091418.01620.gary.stainburn@ringways.co.uk>
From: "MS (direkt)" <martin.stoecker@stb-datenservice.de>
Message-ID: <5abe34aa-b5a3-7ad4-7e62-960ab86e201e@stb-datenservice.de>
Date: Wed, 10 Oct 2018 11:13:15 +0200
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:60.0) Gecko/20100101
 Thunderbird/60.2.1
MIME-Version: 1.0
In-Reply-To: <201810091418.01620.gary.stainburn@ringways.co.uk>
Content-Type: multipart/alternative;
 boundary="------------70B5059171F9166CC3CD7BCD"
Content-Language: de
Precedence: bulk

This is a multi-part message in MIME format.
--------------70B5059171F9166CC3CD7BCD
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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

Am 09.10.2018 um 15:18 schrieb Gary Stainburn:
> 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 ...........
>

-- 

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


--------------70B5059171F9166CC3CD7BCD
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    select distinct s_regno, max(s_stock_no) over (partition by s_regno
    order by s_regno) from stock;<br>
    <br>
    <div class="moz-cite-prefix">Am 09.10.2018 um 15:18 schrieb Gary
      Stainburn:<br>
    </div>
    <blockquote type="cite"
      cite="mid:201810091418.01620.gary.stainburn@ringways.co.uk">
      <pre class="moz-quote-pre" wrap="">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 ...........

</pre>
    </blockquote>
    <br>
    <pre class="moz-signature" cols="72">-- 

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

--------------70B5059171F9166CC3CD7BCD--