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

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