How to obtain the maximum value of a date, between 3 tables...

On Wed, Dec 8, 2010 at 7:15 AM, Andre Lopes <lopes80andre@gmail.com> wrote:

Hi,

I need to obtain the maximum value of a date, but that comparison will be
made between 3 tables... I will explain better with a query...

[code]
select
a.last_refresh_date as d1, ae.last_refresh_date as d2, ha.last_refresh_date
as d3
from tbl1 a
join tbl2 ae on a.id_anuncio_externo = ae.id_anuncio_externo
join tbl3 ha on a.id_anuncio_externo = ha.id_anuncio_externo
[/code]

My question is how is the best way to obtain with date is the greatest, d1,
d2 or d3

If you don't need to know which table it came from I would probably try
select a.last_refresh_date as d1, NULL as d2, NULL as d3 FROM tbl1 as a
UNION ALL
...

--
Jon

Hi,

Thanks for the reply.

And there are other options to do it without using a UNION? I don't need to
know from witch table comes the greatest date, but the query is complex,
this query is part of an UNION. The use of the CASE WHEN could be an
alternative?

Best Regards,

On Wed, Dec 8, 2010 at 1:20 PM, Jon Nelson
<jnelson+pgsql@jamponi.net<jnelson%2Bpgsql@jamponi.net>

On Wed, Dec 8, 2010 at 5:15 AM, Andre Lopes <lopes80andre@gmail.com> wrote:

Hi,

I need to obtain the maximum value of a date, but that comparison will be
made between 3 tables... I will explain better with a query...

[code]
select
a.last_refresh_date as d1, ae.last_refresh_date as d2, ha.last_refresh_date
as d3
from tbl1 a
join tbl2 ae on a.id_anuncio_externo = ae.id_anuncio_externo
join tbl3 ha on a.id_anuncio_externo = ha.id_anuncio_externo
[/code]

My question is how is the best way to obtain with date is the greatest, d1,
d2 or d3

Can you guys give me a clue.

Best Regards,

How about using GREATEST?

http://www.postgresql.org/docs/8.4/interactive/functions-conditional.html
(9.16.4)

select GREATEST(d1,d2,d3)
from (...)

-----Original Message-----
From: Andre Lopes [mailto:lopes80andre@gmail.com]
Sent: Wednesday, December 08, 2010 8:16 AM
To: postgresql Forums
Subject: How to obtain the maximum value of a date, between 3
tables...

Hi,

I need to obtain the maximum value of a date, but that
comparison will be made between 3 tables... I will explain
better with a query...

[code]
select
a.last_refresh_date as d1, ae.last_refresh_date as d2,
ha.last_refresh_date as d3 from tbl1 a join tbl2 ae on
a.id_anuncio_externo = ae.id_anuncio_externo join tbl3 ha on
a.id_anuncio_externo = ha.id_anuncio_externo [/code]

My question is how is the best way to obtain with date is the
greatest, d1, d2 or d3

Can you guys give me a clue.

Best Regards,

This:

SELECT GREATEST(q.d1, q.d2, q.d3) FROM
(select
a.last_refresh_date as d1, ae.last_refresh_date as d2,
ha.last_refresh_date as d3 from tbl1 a join tbl2 ae on
a.id_anuncio_externo = ae.id_anuncio_externo join tbl3 ha on
a.id_anuncio_externo = ha.id_anuncio_externo) q;

should do it.

Igor Neyman

Le mercredi 08 dￜcembre 2010 ￜ 13:35 +0000, Andre Lopes a ￜcrit :

Hi,

Thanks for the reply.

And there are other options to do it without using a UNION? I don't
need to know from witch table comes the greatest date, but the query
is complex, this query is part of an UNION. The use of the CASE WHEN
could be an alternative?

SELECT 'tbl1',last_refresh_date FROM tbl1 UNION SELECT 'tbl2',
last_refresh_date FROM tbl2 UNION SELECT 'tbl3', last_refresh_date FROM
tbl3 ORDER BY 1 DESC;

--
Vincent Veyron
http://marica.fr/
Progiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique

Le jeudi 09 dￜcembre 2010 ￜ 03:58 +0100, Vincent Veyron a ￜcrit :

SELECT 'tbl1',last_refresh_date FROM tbl1 UNION SELECT 'tbl2',
last_refresh_date FROM tbl2 UNION SELECT 'tbl3', last_refresh_date FROM
tbl3 ORDER BY 1 DESC;

Argh... make that :

ORDER BY 2 DESC;

--
Vincent Veyron
http://marica.fr/
Progiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique

On 2010-12-08, Andre Lopes <lopes80andre@gmail.com> wrote:

--20cf3043476e053b5f0496e5ebc4
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I need to obtain the maximum value of a date, but that comparison will be
made between 3 tables... I will explain better with a query...

you probably want
greatest(d1,d2,d3)
or possibly
max(greatest(d1,d2,d3))

--
⚂⚃ 100% natural