Finding a peak by different values for a field

Question

I am unsure on how to google this, so let me just try to explain. Train numbers are a slot in a timetable, and hence repeat everyday. For each train, there are a set of events from start to finish, and those events include a value for the current weight. What I would like to find out is the peak per train. If I just wanted the peak, it would just be a select *, max(weight)..., but I want it done by train number so that I can get the date/time and station name. I want something along the lines of:

SELECT train, weight, date, station FROM event_table  WHERE date >= '2010/10/03' 
  AND date <= '2010/11/03' ORDER BY weight DESC LIMIT 1 GROUP BY train

But obviously the GROUP BY can't be after the limit. My option is ignoring the limit and filtering it out in PHP, but it's wasted cycles and bandwidth, which might be a problem in the future.

Thanks

Vishal · Answer 1 · 2010-11-04T19:19:27.440

1

If all you want highest of weights for each train-

SELECT train, Max(weight) FROM event_table  WHERE date >= '2010/10/03' 
  AND date <= '2010/11/03' GROUP BY train

edited Nov 04 '10 at 19:19

answered Nov 04 '10 at 19:11

Vishal

12,133
17
82
128

score 1 · Answer 2 · answered Nov 04 '10 at 19:21

something like this should do it:

select t.train, t2.wt, t.date, t.station
from event_table t, ( SELECT train, max(weight) wt
FROM event_table  
WHERE date >= '2010/10/03'  
  AND date <= '2010/11/03' 
GROUP BY train 
) t2
where t.train = t2.train
and t.weight = t2.weight

note this allows for the same max weight at multiple stations.

score 1 · Accepted Answer · answered Nov 04 '10 at 19:23

1

As an alternative to Randy's answer you use Join syntax.

SELECT * 
FROM 
event_table   et
INNER JOIN (
    SELECT 
        train, 
        Max(weight) weight
    FROM 
       event_table  
    WHERE date >= '2010/10/03' 
      AND date <= '2010/11/03' 
    GROUP BY train) max 
ON et.train = max.train  and et.weight = max.weight

answered Nov 04 '10 at 19:23

Conrad Frix

51,984
12
96
155

I've thought of those. I guess I'll have to content myself in filtering the duplicate trains. – Nightwish Nov 05 '10 at 11:18

score 0 · Answer 4 · answered Nov 04 '10 at 20:02

0

I'm not sure if this is for MySQL or Postgress, but I think that this query may help:

SELECT train, 
max(weight) OVER (PARTITION BY train) 
FROM event_table  ;

*It works at postgres from version 8.4, not sure about MySQL

answered Nov 04 '10 at 20:02

MaLo

11
1

Sadly, it's MySql, so that doesn't exist. – Nightwish Nov 05 '10 at 11:17

score 0 · Answer 5 · edited May 23 '17 at 11:56

I seem to have improved my Google Foo. I found this question and have come up with an answer. For reference:

SELECT r2.train, r2.wight, r2.date, r2.station
FROM   (
       SELECT (
              SELECT  id
              FROM    event_table ri
              WHERE   ri.train = r1.train and date >= '2010-10-01' AND date <= '2010-12-01' 
              ORDER BY
                      ri.weight DESC
              LIMIT 1
              ) rid
       FROM   (
              SELECT DISTINCT train
              FROM event_table 
              where date >= '2010-10-01' AND date <= '2010-12-01'
              ) r1
) ro, event_table r2
WHERE  r2._id = ro.rid

Finding a peak by different values for a field

5 Answers5