CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
Firstname
Firstname

Reputation: 365

Select the max of multiple values and take the min

I have 5 tables in my database each having a timestamp column called updated_at.

I want to write a query that would fetch the max of updated_at from all the 5 tables and then take the min of those 5 returned results.

I was trying to write a subquery that would get max of updated_at of the 5 tables and then use a case statement on top the query to get the min.

Is there any other better approach ?.

I'm using postgres & Redshift

Upvotes: 0

Views: 1747

Answers (4)

Vecchiasignora
Vecchiasignora

Reputation: 1315

you dont need to use subquery, you can join your tables select max 5 columns and after that select min, look this

select case when max(a.updated_at)>max(b.updated_at) then max(b.updated_at) end, ..... from a join b join c join d join e

BUT !!! subquery will be the best approach because you dont need to calculate max match time on same column

Upvotes: 0

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

Try this

select min(maxdate) as Mindate
( select max(updated_at) as maxdate from table1
  union all
select max(updated_at) from table2
  union all
select max(updated_at) from table3
  union all
select max(updated_at) from table4
  union all
select max(updated_at) from table5 ) mintable

Upvotes: 1

Phylyp
Phylyp

Reputation: 1689

How about: 

SELECT MIN(max_updated_at) as min_updated_at FROM ( 
  SELECT MAX(updated_at) AS max_updated_at FROM table1 
  UNION ALL 
  SELECT MAX(updated_at) AS max_updated_at FROM table2 
  UNION ALL 
  SELECT MAX(updated_at) AS max_updated_at FROM table3 
  UNION ALL 
  SELECT MAX(updated_at) AS max_updated_at FROM table4 
  UNION ALL 
  SELECT MAX(updated_at) AS max_updated_at FROM table5 
) AS max_updated_at_values

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

You could use union all in subquery to get all the max values from each table then take min of that

Select min(updated_at) from
(
  Select max(updated_at) as updated_at from table1
  Union all
  Select max(updated_at) as updated_at from table2
  Union all
  . . .
) t;

Upvotes: 3

Related Questions