Combining MySQL querys

Question

This SQL tells me how much when the maximum occurred in the last hour, and is easily modified to show the same for the minimum.

SELECT
  mt.mB as Hr_mB_Max,
  mt.UTC as Hr_mB_Max_when
FROM
  thundersense mt
WHERE
  mt.mB =(
  SELECT
    MAX(mB)
  FROM
    thundersense mt2
  WHERE
    mt2.UTC >(UNIX_TIMESTAMP() -3600))
  ORDER BY
    utc
  DESC
LIMIT 1

How do I modify it so it returns both maximum & minimum and their respective times?

Yours Simon M.

score 1 · Answer 1 · answered Feb 17 '18 at 17:20

1

One method uses a join:

SELECT mt.mB as Hr_mB_Max, mt.UTC as Hr_mB_Max_when
FROM thundersense mt JOIN
     (SELECT MAX(mB) as max_mb, MIN(mb) as min_mb
      FROM thundersense mt2
      WHERE mt2.UTC >(UNIX_TIMESTAMP() - 3600)
     ) mm
     ON mt.mB IN (mm.max_mb, mm.min_mb)
ORDER BY utc DESC;

My only concern is your limit 1. Presumably, the mBs should be unique. If not, there is a bit of a challenge. One possibility would be to use an auto-incremented id rather than mB.

answered Feb 17 '18 at 17:20

Gordon Linoff

1,242,037
58
646
786

Thanks for the quick response Gordon, – Simon Markham Feb 17 '18 at 18:31
I think I need to make myself a little clearer, The query I use shows the max from the last hour along with its time stamp. I can re-run the query only looking for the min and its time stamp. What I would like is to get just 1 row of 4 columns: Max with its Time and Min with its Time. I thought that I could select all the records from the last hour then query that. – Simon Markham Feb 17 '18 at 18:40

cdncat · Accepted Answer · 2018-02-18T02:39:15.397

Based on my understanding of your question, you are looking to create a 4 column and 1 row answer where it looks like:

+-------+-----------------+----------+-----------------+
| event | time_it_occured |  event   | time_it_occured |
+-------+-----------------+----------+-----------------+
| fun   |           90000 | homework |           12000 |
+-------+-----------------+----------+-----------------+

Below is a similar situation/queries you can adapt for your situation.

So, given a table called 'people' that looks like:

+----+------+--------+
| ID | name | salary |
+----+------+--------+
|  1 | bob  |  40000 |
|  2 | cat  |  12000 |
|  3 | dude |  50000 |
+----+------+--------+

You can use this query:

SELECT * FROM 
(SELECT name, salary FROM people WHERE salary = (SELECT MAX(salary) FROM people)) t JOIN
(SELECT name, salary FROM people WHERE salary = (SELECT MIN(salary) FROM people)) a;

to generate:

+------+--------+------+--------+
| name | salary | name | salary |
+------+--------+------+--------+
| bob  |  40000 | cat  |  12000 |
+------+--------+------+--------+

Some things to note:

you can change the WHERE clauses to be the ones you have mentioned in question (for MAX and MIN).
Please be careful with the above query, here I am using a cartesian join (cross join in MYSQL) in order to get the 4 columns. To be honest, it doesn't make sense for me to get back data in one row but you said that's what you're looking for.

Here is what I would work with instead, getting two tuples/rows back:

+----------+--------+
| name     | salary |
+----------+--------+
| dude     | 95000  |
| Cat      | 12000  |
+----------+--------+

And to generate this, you would use:

(SELECT name, salary FROM instructor WHERE salary = (SELECT MAX(salary) FROM instructor))
UNION
(SELECT name, salary FROM instructor WHERE salary = (SELECT MIN(salary) FROM instructor));

Also: A JOIN without a ON clause is just a CROSS JOIN. How to use mysql JOIN without ON condition?

Many thanks for your solutions, they are closer to what I wanted. — Simon Markham, Feb 18 '18 at 14:27
I was surprised to see that there is no saving in time between running 'find max' then 'find min' and using these solutions though. I thought that it might be more efficient to combine the searches — Simon Markham, Feb 18 '18 at 14:34

Combining MySQL querys

2 Answers2