CODEWITHSUNDEEP
mysqlsql
Simon Markham
Simon Markham

Reputation: 81

Combining MySQL querys

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.

Upvotes: 1

Views: 75

Answers (2)

cdncat
cdncat

Reputation: 432

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:

  1. you can change the WHERE clauses to be the ones you have mentioned in question (for MAX and MIN).
  2. 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?

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271051

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.

Upvotes: 1

Related Questions