Geek Answers Handbook

Mysql selects the largest field value from adjacent rows

When asking a question for the first time, please be careful with me :)

I have 2 tables:

  • "Master" is an ever-growing magazine
  • "Current" - one line updated with the last record

I am trying to select the largest "peak" row in an adjacent block (name) of rows from "master" that match the "name" in the second table (one row). This contiguous block must be the last contiguous block in the table.

Thus, the correct result would be:

17   | 2016-03-12 23:57:00 | 190    | Tracey

Teacher:

id   | date time           | peak   | name
-------------------------------------------------
01   | 2016-03-12 23:41:00 | 201    | Tracey
02   | 2016-03-12 23:42:00 | 107    | Tracey
03   | 2016-03-12 23:43:00 | 103    | Tracey
04   | 2016-03-12 23:44:00 | 195    | Tracey
05   | 2016-03-12 23:45:00 | 134    | Tracey
06   | 2016-03-12 23:46:00 | 144    | Ian
07   | 2016-03-12 23:47:00 | 155    | Ian
08   | 2016-03-12 23:48:00 | 166    | Ian
09   | 2016-03-12 23:49:00 | 132    | Ian
10   | 2016-03-12 23:50:00 | 112    | Ian
11   | 2016-03-12 23:51:00 | 143    | Steve
12   | 2016-03-12 23:52:00 | 165    | Steve
13   | 2016-03-12 23:53:00 | 122    | Steve
14   | 2016-03-12 23:54:00 | 123    | Steve
15   | 2016-03-12 23:55:00 | 132    | Steve
16   | 2016-03-12 23:56:00 | 143    | Steve
17   | 2016-03-12 23:57:00 | 190    | Tracey ----
18   | 2016-03-12 23:58:00 | 165    | Tracey    |   
19   | 2016-03-12 23:59:00 | 154    | Tracey    | latest 
20   | 2016-03-13 00:00:00 | 131    | Tracey    | contiguous
21   | 2016-03-13 00:01:00 | 167    | Tracey    | block
22   | 2016-03-13 00:02:00 | 178    | Tracey ----

Current:

id   | date time           | peak   | name
---------------------------------------------
01   | 2016-03-13 00:02:00 | 178    | Tracey

Update for @EhsanT

I tried:

SET @name = 'Tracey';
SELECT * FROM `Master`
WHERE `name` = @name
AND `id` > (
    SELECT max(`id`)
    FROM `Master`
    WHERE `name` != @name
    AND `id` < (
        SELECT max(`id`)
        FROM `Master`
        WHERE `name` = @name
    )
)
ORDER BY peak DESC
LIMIT 1
+4
source share
2 answers

Best practice for this answer is to use procedure.

procedures, , , , , , , Master , Master.

, Master , , :

select max(`peak`)
from `Master`
where `name` = 'Tracey'
and `id` > (
  select max(`id`)
  from `Master`
  where `name` != 'Tracey'
  and `id` < (
    select max(`id`)
    from `Master`
    where `name` = 'Tracey'
  )
)

, , , peak 1.

, :

select *
from `Master`
where `name` = 'Tracey'
and `id` > (
  select max(`id`)
  from `Master`
  where `name` != 'Tracey'
  and `id` < (
    select max(`id`)
    from `Master`
    where `name` = 'Tracey'
  )
)
order by peak desc
limit 1
+3
Select * from master where peak = (Select MAX(peak) from master where name = (Select name from current))
0

More articles:


All Articles