agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedleft outer join to pull in most recent record
2+ messages / 2 participants
[nested] [flat]
* left outer join to pull in most recent record
@ 2018-10-09 13:18 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 2+ messages in thread
From: Gary Stainburn @ 2018-10-09 13:18 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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 ...........
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: left outer join to pull in most recent record
@ 2018-10-10 09:13 MS (direkt) <martin.stoecker@stb-datenservice.de>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 2+ messages in thread
From: MS (direkt) @ 2018-10-10 09:13 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-10-10 09:13 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-10-09 13:18 left outer join to pull in most recent record Gary Stainburn <gary.stainburn@ringways.co.uk>
2018-10-10 09:13 ` MS (direkt) <martin.stoecker@stb-datenservice.de>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox