CODEWITHSUNDEEP
sqlpostgresql
Chin
Chin

Reputation: 20675

How to get the max of different columns

I'm having two tables like this:

LastDownloaded:

Movie    Date-last-downloaded
 A              1-1-1111
 B              2-2-2222
 C              3-3-3333
 D              4-4-4444
 E              5-5-5555

LastPurchased:

Movie    Date-last-purchased
 A              0-0-0000
 B              3-3-3333
 C              5-5-5555
 G              2-2-2222
 H              7-7-7777

and I'm trying to find the last download OR purchase of each movie. That is:

how can I do it?

The end result should be something like this:

Movie    Date-last-purchased-or-downloaded
 A              1-1-1111
 B              3-3-3333
 C              5-5-5555
 D              4-4-4444
 E              5-5-5555
 G              2-2-2222
 H              7-7-7777

(the question name is misleading - I would appreciate it if someone would think of a better one and fix it for me)

Upvotes: 2

Views: 63

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

select
    coalesce(lp.movie, ld.movie) movie,
    greatest(
        date_last_downloaded,
        date_last_purchased
    ) Date_last_purchased_or_downloaded
from
    lastdownloaded ld
    full outer join
    lastpurchased lp on ld.movie = lp.movie

Upvotes: 1

Andomar
Andomar

Reputation: 238086

select  movie
,       max(dt)
from    (
        select  Movie
        ,       Date-last-downloaded as dt
        from    LastDownloaded
        union all
        select  Movie
        ,       Date-last-purchased
        from    LastPurchased
        ) SubQueryAlias
group by
        movie

Upvotes: 2

Related Questions