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

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

From
Andre Lopes
Date:
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,

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

From
Jon Nelson
Date:
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

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

From
Andre Lopes
Date:
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> wrote:
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

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

From
bricklen
Date:
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 (...)

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

From
"Igor Neyman"
Date:

> -----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

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

From
Vincent Veyron
Date:
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


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

From
Vincent Veyron
Date:
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


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

From
Jasen Betts
Date:
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