From: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: left outer join only select newest record
Date: Thu, 24 May 2012 09:27:56 +0100
Message-ID: <58025C771D674FCC8A6F2C08E8D10CF6@marktestcr.marktest.pt> (raw)
Hi, Gary,
Unless I'm mistaken this didn't give what you need.
Could you please tell me (if you have time) the error returned or wrong
result, just for my own understanding of where I've gone sideways on
this...?
Best,
Oliver
----- Original Message -----
From: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>
To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>; "Gary
Stainburn" <gary.stainburn@ringways.co.uk>; <pgsql-sql@postgresql.org>
Sent: Wednesday, May 23, 2012 11:41 AM
Subject: Re: [SQL] left outer join only select newest record
> Sorry, Gary,
>
> I made a mistake on the last column.
>
> It should be
>
> SELECT subq.s_stock_no,subq.s_regno,subq.s_vin,subq.s_created,subq.m,
> sec.ud_handover_date
> FROM
> (select s_stock_no, s_regno, s_vin, s_created, MAX(ud_id) as m
> from stock s
> left outer join used_diary u
> on s.s_regno = u.ud_pex_registration
> where s_stock_no = 'UL15470';
> GROUP s_stock_no,s_regno,s_vin,s_created
> ) subq
> JOIN
> used_diary sec
> ON subq.m = sec.ud_id
>
> Best,
> Oliver
> ----- Original Message -----
> From: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>
> To: "Gary Stainburn" <gary.stainburn@ringways.co.uk>;
> <pgsql-sql@postgresql.org>
> Sent: Wednesday, May 23, 2012 11:29 AM
> Subject: Re: [SQL] left outer join only select newest record
>
>
>> Hello again, Gary,
>>
>> I don't know if this query works OK, i havent tried it.
>>
>> But, If I understood correctly this can be one way to do what you want.
>> Could you please tell me if it worked and if it didn't why, so we can
>> tweak it.
>>
>> Best,
>> Oliver
>>
>> SELECT subq.s_stock_no,subq.s_regno,subq.s_vin,subq.s_created,subq.m,
>> sec.s_creacted
>> FROM
>> (select s_stock_no, s_regno, s_vin, s_created, MAX(ud_id) as m
>> from stock s
>> left outer join used_diary u
>> on s.s_regno = u.ud_pex_registration
>> where s_stock_no = 'UL15470';
>> GROUP s_stock_no,s_regno,s_vin,s_created
>> ) subq
>> JOIN
>> used_diary sec
>> ON subq.m = sec.ud_id
>>
>> ----- Original Message -----
>> From: "Gary Stainburn" <gary.stainburn@ringways.co.uk>
>> To: <pgsql-sql@postgresql.org>
>> Sent: Wednesday, May 23, 2012 10:47 AM
>> Subject: Re: [SQL] left outer join only select newest record
>>
>>
>>> Appologies for not making it clearer. stock_details is simply a view of
>>> table
>>> stock, pulling in some lookup values. used_diary is the name of the
>>> table
>>> containing the tax requests. It's called the used_diary because it was
>>> the
>>> diary for taxing used vehicles.
>>>
>>> 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)
>>>
>>>
>>> On Wednesday 23 May 2012 10:37:31 Oliveiros d'Azevedo Cristina wrote:
>>>> Gary,
>>>>
>>>> You describe two tables vehicle stock and tax requests. The former has
>>>> a
>>>> one-to-many relationship wit the second one, right?
>>>>
>>>> But your query involves stock details and used_diary.
>>>>
>>>> What is the relationship of these two new tables to the previous ones?
>>>>
>>>> Could you please kindly supply an example of what you have and of the
>>>> desired output? For me it would be easier...
>>>>
>>>> Best,
>>>> Oliver
>>>>
>>>> ----- Original Message -----
>>>> From: "Gary Stainburn" <gary.stainburn@ringways.co.uk>
>>>> To: <pgsql-sql@postgresql.org>
>>>> Sent: Wednesday, May 23, 2012 10:27 AM
>>>> Subject: [SQL] left outer join only select newest record
>>>>
>>>> > Hi folks,
>>>> >
>>>> > I know I've seen posts like this before but Google isn't helping
>>>> > today.
>>>> >
>>>> > I have two tables, vehicle stock and tax requests. Each vehicle can
>>>> > be
>>>> > taxed
>>>> > more than once, but I only want to pull in the most recent tax
>>>> > request -
>>>> > the
>>>> > one with the highest ud_id.
>>>> >
>>>> > I have the following, which obviously returning multiple records
>>>> > which
>>>> > then
>>>> > appears that the same vehicle is in stock multiple times. How can I
>>>> > make
>>>> > it
>>>> > so we only show each vehicle once, showing the most recent tax
>>>> > request
>>>> > details.
>>>> >
>>>> >
>>>> > select * from stock_details s
>>>> > left outer join used_diary u on s.s_registration = u.ud_registration;
>>>> >
>>>> >
>>>> > --
>>>> > Gary Stainburn
>>>> > Group I.T. Manager
>>>> > Ringways Garages
>>>> > http://www.ringways.co.uk
>>>> >
>>>> > --
>>>> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>>>> > To make changes to your subscription:
>>>> > http://www.postgresql.org/mailpref/pgsql-sql
>>>
>>>
>>>
>>> --
>>> Gary Stainburn
>>> Group I.T. Manager
>>> Ringways Garages
>>> http://www.ringways.co.uk
>>>
>>> --
>>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
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: oliveiros.cristina@marktest.pt, gary.stainburn@ringways.co.uk
Subject: Re: left outer join only select newest record
In-Reply-To: <58025C771D674FCC8A6F2C08E8D10CF6@marktestcr.marktest.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox