Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Re: [SQL] left outer join only select newest record
4 views
Skip to first unread message
Oliveiros d'Azevedo Cristina
May 23, 2012, 5:37:31 AM5/23/12
to
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.st...@ringways.co.uk>
To: <pgsq...@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 (pgsq...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
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.st...@ringways.co.uk>
To: <pgsq...@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 (pgsq...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Pavel Stehule
May 23, 2012, 5:46:02 AM5/23/12
to
2012/5/23 Gary Stainburn <gary.st...@ringways.co.uk>:
... order by u.ud_id desc
or
select *
from stock_details s
left join (select * from used_diary where (ud_id,
ud_registration) = (select max(ud_id), ud_registration from used_diary
group by ud_registration)) x
on s.s_registration = x.ud_registration;
Regards
Pavel Stehule
> 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;
select distinct on (s.s_registration) *
>
> 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;
... order by u.ud_id desc
or
select *
from stock_details s
ud_registration) = (select max(ud_id), ud_registration from used_diary
group by ud_registration)) x
on s.s_registration = x.ud_registration;
Regards
Pavel Stehule
Thomas Kellerer
May 23, 2012, 6:22:56 AM5/23/12
to
Gary Stainburn, 23.05.2012 11:47:
> 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,
> 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)
>
Something like:
select *
from (
select s_stock_no,
s_regno
s_vin,
s_created,
ud_id,
ud_handover_date,
row_number() over (partition by s_stock_no order by ud_id desc) as rn
from stock s
left outer join used_diary u on s.s_regno = u.ud_pex_registration
where s_stock_no = 'UL15470'
) t
where rn = 1
The "partition by s_stock_no order" isn't really necessary as your where clause already limits that to a single stock_no.
But in case you change that statement to return more than one stock_no in the future it will be necessary.
> 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)
>
Something like:
select *
from (
select s_stock_no,
s_regno
s_vin,
s_created,
ud_id,
ud_handover_date,
row_number() over (partition by s_stock_no order by ud_id desc) as rn
from stock s
left outer join used_diary u on s.s_regno = u.ud_pex_registration
where s_stock_no = 'UL15470'
) t
where rn = 1
The "partition by s_stock_no order" isn't really necessary as your where clause already limits that to a single stock_no.
But in case you change that statement to return more than one stock_no in the future it will be necessary.
Oliveiros d'Azevedo Cristina
May 23, 2012, 6:29:27 AM5/23/12
to
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
) subq
JOIN
used_diary sec
ON subq.m = sec.ud_id
----- Original Message -----
From: "Gary Stainburn" <gary.st...@ringways.co.uk>
To: <pgsq...@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.
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
where s_stock_no = 'UL15470';
) subq
JOIN
used_diary sec
ON subq.m = sec.ud_id
----- Original Message -----
From: "Gary Stainburn" <gary.st...@ringways.co.uk>
To: <pgsq...@postgresql.org>
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)
>
>
> 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)
>
>
Oliveiros d'Azevedo Cristina
May 23, 2012, 6:41:04 AM5/23/12
to
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
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
(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
Pavel Stehule
May 24, 2012, 4:17:00 AM5/24/12
to
>
> This was more like what I was thinking, but I still get an error, which I
> don't understand. I have extracted the inner sub-select and it does only
> return one record per registration. (The extra criteria is just to ignore old
> or cancelled tax requests and doesn't affect the query)
>
> goole=# select distinct on (s.s_stock_no) s_stock_no, s_regno, s_vin,
> s_created, ud_id, ud_handover_date from stock s left outer join (select
> ud_id, ud_pex_registration, ud_handover_date from used_diary where (ud_id,
> ud_pex_registration) = (select max(ud_id), ud_pex_registration from
> used_diary where (ud_tab is null or ud_tab <> 999) and ud_created >
> CURRENT_DATE-'4 months'::interval group by ud_pex_registration)) udIn on
> s.s_regno = udIn.ud_pex_registration;
> ERROR: more than one row returned by a subquery used as an expression
sure, I am sorry
please, WHERE (ud_id, ud_pex_registration) = (SELECT ...
replace by
WHERE (..) IN (SELECT ..
Regards
Pavel
> This was more like what I was thinking, but I still get an error, which I
> don't understand. I have extracted the inner sub-select and it does only
> return one record per registration. (The extra criteria is just to ignore old
> or cancelled tax requests and doesn't affect the query)
>
> goole=# select distinct on (s.s_stock_no) s_stock_no, s_regno, s_vin,
> s_created, ud_id, ud_handover_date from stock s left outer join (select
> ud_id, ud_pex_registration, ud_handover_date from used_diary where (ud_id,
> ud_pex_registration) = (select max(ud_id), ud_pex_registration from
> used_diary where (ud_tab is null or ud_tab <> 999) and ud_created >
> CURRENT_DATE-'4 months'::interval group by ud_pex_registration)) udIn on
> s.s_regno = udIn.ud_pex_registration;
> ERROR: more than one row returned by a subquery used as an expression
sure, I am sorry
please, WHERE (ud_id, ud_pex_registration) = (SELECT ...
replace by
WHERE (..) IN (SELECT ..
Regards
Pavel
Gary Stainburn
May 24, 2012, 4:21:43 AM5/24/12
to
On Thursday 24 May 2012 09:17:00 Pavel Stehule wrote:
> please, WHERE (ud_id, ud_pex_registration) = (SELECT ...
>
> replace by
>
> WHERE (..) IN (SELECT ..
>
> Regards
>
> Pavel
Worked perfectly, thank you
> please, WHERE (ud_id, ud_pex_registration) = (SELECT ...
>
> replace by
>
> WHERE (..) IN (SELECT ..
>
> Regards
>
> Pavel
Oliveiros d'Azevedo Cristina
May 24, 2012, 4:27:56 AM5/24/12
to
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...@marktest.pt>
To: "Oliveiros d'Azevedo Cristina" <oliveiros...@marktest.pt>; "Gary
Stainburn" <gary.st...@ringways.co.uk>; <pgsq...@postgresql.org>
Sent: Wednesday, May 23, 2012 11:41 AM
Subject: Re: [SQL] left outer join only select newest record
>> from stock s
>>> select s_stock_no, s_regno, s_vin, s_created, ud_id, ud_handover_date
>>> from
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...@marktest.pt>
To: "Oliveiros d'Azevedo Cristina" <oliveiros...@marktest.pt>; "Gary
Stainburn" <gary.st...@ringways.co.uk>; <pgsq...@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
>
> 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
0 new messages