CODEWITHSUNDEEP
mysql
3kstc
3kstc

Reputation: 1956

How to get max value with various conditions from a single MySQL table

I have table with a bunch of (machine id) mid's and (sensor id) sid's, and their corresponding (values) v's. Needless to say the id column is a unique row number. (NB: There are other columns in the table, and not all mid's have the same sid's)

Current Table:

+------+-------+-------+-----+---------------------+
|  id  |  mid  |  sid  |  v  |      timestamp      |
+------+-------+-------+-----+---------------------+
|  51  |  10   |   1   |  40 |  2015/5/1 11:56:01  |
|  52  |  10   |   2   |  39 |  2015/5/1 11:56:25  |
|  53  |  10   |   2   |  40 |  2015/5/1 11:56:42  |
|  54  |  11   |   1   |  50 |  2015/5/1 11:57:52  |
|  55  |  11   |   2   |  18 |  2015/5/1 11:58:41  |
|  56  |  11   |   2   |  19 |  2015/5/1 11:58:59  |
|  57  |  11   |   3   |  58 |  2015/5/1 11:59:01  |
|  58  |  11   |   3   |  65 |  2015/5/1 11:59:29  |
+------+-------+-------+-----+---------------------+

Q: How would I get the MAX(v)for each sid for each mid?

Expected Output:

+------+-------+-------+-----+---------------------+
|  id  |  mid  |  sid  |  v  |      timestamp      |
+------+-------+-------+-----+---------------------+
|  51  |  10   |   1   |  40 |  2015/5/1 11:56:01  |
|  53  |  10   |   2   |  40 |  2015/5/1 11:56:42  |
|  54  |  11   |   1   |  50 |  2015/5/1 11:57:52  |
|  56  |  11   |   2   |  19 |  2015/5/1 11:58:59  |
|  58  |  11   |   3   |  65 |  2015/5/1 11:59:29  |
+------+-------+-------+-----+---------------------+

The expected output is to obtain the whole row with all the (single) max value for all the sids in all the mids.

Addendum:

Due to a very big table, I need to place boundaries with dates. For the sample above the two boundary dates should be 2015/05/01 00:00:00 (1st of May'15) till 2015/05/02 00:00:00 (2nd of May'15). Q: How could I add this date boundary?

Upvotes: 1

Views: 61

Answers (2)

Alexander
Alexander

Reputation: 4527

Use MAX() function with GROUP BY clause 

SELECT id,  mid,  sid, MAX(v) AS v, `timestamp` 
FROM MyTable 
GROUP BY mid,  sid;

This returns rows with maximum values of v for each combination of mid and sid.

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

Find the max v in subquery for each combination of mid, sid and then join it with your original table to get the desired result.

select *
from your_table t
join (
    select mid, sid, max(v) as v
    from your_table
    group by mid, sid
) t2 using (mid, sid, v);

Note here that if there are multiple rows with same sid, mid and v, it will return all of them.

As mentioned in the comments, since you have an id column, you can include that in limited correlated query like this:

select *
from your_table t1
where id = (select id
            from your_table t2
            where t1.mid = t2.mid
            and t1.sid = t2.sid
            order by v desc, id desc
            limit 1
            );

This will give you one single row per mid, sid combination with max v (and latest id in case of ties).

Upvotes: 2

Related Questions