Nested Nested? MySQL query help

Question

Long story short, I'm trying to get the time my site was last updated. The main content of my site is stored in four tables. Each of these tables has an updated_at field.

I'm able to combine them in a nested query:

SELECT (
    SELECT updated_at FROM proposition ORDER BY updated_at DESC LIMIT 1
) as latest_proposition,
(
    SELECT updated_at FROM product ORDER BY updated_at DESC LIMIT 1
) as latest_product,
(
    SELECT updated_at FROM review ORDER BY updated_at DESC LIMIT 1
) as latest_review,
(
    SELECT updated_at FROM segment ORDER BY updated_at DESC LIMIT 1 
) as latest_segment

..which gives me something like..


-------------------+-------------------+-------------------+-------------------
latest_proposition |latest_product     |latest_review      |latest_segment     
-------------------+-------------------+-------------------+-------------------
2011-05-25 14:45:30|2011-05-25 14:37:28|2011-05-12 09:20:19|2011-05-12 09:20:19
-------------------+-------------------+-------------------+-------------------
1 row in set (0.00 sec)

My question is - How do I now get the latest of these fields? To get the overall "site last updated" time.

Thanks in advance!

I think you are after a union.

Andrew
– Andrew

2011-05-26 10:43:40 +00:00
Commented May 26, 2011 at 10:43 — Andrew
– Andrew, Commented May 26, 2011 at 10:43

GolezTrol · Accepted Answer · 2011-05-26 10:47:51Z

4

select
    max(updated_at) as updated_at
from
(
    SELECT max(updated_at) as updated_at FROM proposition
    UNION
    SELECT max(updated_at) FROM product
    UNION
    SELECT max(updated_at) FROM review
    UNION
    SELECT max(updated_at) FROM segment
) x

answered May 26, 2011 at 10:47

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Johan Over a year ago

@GolexTrol, use a UNION ALL otherwise MySQL will (try to) eliminate duplicate rows which will slow the query down.

GolezTrol Over a year ago

Yes, thats true. Through in in this, trying to eliminate duplicates from total of 4 will not be that much a hit. Besides, less rows, will speed up retrieving the max in the main query. (all theoretically when dealing with just 4 rows). But a good point indeed. UNION ALL is usually better and hardly ever worse than UNION.

Oswald · Accepted Answer · 2011-05-26 10:53:32Z

3

Use GREATEST() on the returned fields (after passing them through COALESCE(), if you expect NULL values).

edited May 26, 2011 at 10:53

answered May 26, 2011 at 10:46

Oswald

31.8k3 gold badges45 silver badges72 bronze badges

Comments

davek · Accepted Answer · 2011-05-26 10:46:29Z

0

SELECT updated_at 
from 
(
    SELECT updated_at FROM proposition ORDER BY updated_at DESC LIMIT 1
          union all
    SELECT updated_at FROM product ORDER BY updated_at DESC LIMIT 1
          union all
    SELECT updated_at FROM review ORDER BY updated_at DESC LIMIT 1
          union all
    SELECT updated_at FROM segment ORDER BY updated_at DESC LIMIT 1 
) x
order by updated_at limit 1

answered May 26, 2011 at 10:46

davek

23k11 gold badges80 silver badges97 bronze badges

Comments

tofutim · Accepted Answer · 2011-05-26 10:47:40Z

0

SELECT MAX(updated_at)
FROM (SELECT MAX(updated_at) FROM proposition UNION ALL
  SELECT MAX(updated_at) FROM product UNION ALL
  SELECT MAX(updated_at) FROM review UNION ALL
  SELECT MAX(updated_at) FROM segment) x

answered May 26, 2011 at 10:47

tofutim

23.6k20 gold badges91 silver badges159 bronze badges

1 Comment

GolezTrol Over a year ago

You'll need to alias the field in the subselect. As I did ;*)

Collectives™ on Stack Overflow

Nested Nested? MySQL query help

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related