CODEWITHSUNDEEP
mysql
Capt Otis
Capt Otis

Reputation: 1260

MySql selecting greatest difference between 2 rows within the past day

I have a table that sort of looks like this

id  |  name  |  c1  |  c2  |  c3  |  c4  |  time
-------------------------------------------------
 1  |  miley |  23  |  11  |  21  |  18  |  2013-01-13 20:26:25
 2  |  john  |  31  |  29  |  23  |  27  |  2013-01-14 20:26:25
 3  |  steve |  44  |  31  |  33  |  35  |  2013-01-14 20:26:25
 4  |  miley |  34  |  44  |  47  |  48  |  2013-01-15 08:26:25
 5  |  john  |  27  |  53  |  49  |  52  |  2013-01-15 08:26:25
 6  |  steve |  27  |  62  |  50  |  64  |  2013-01-16 08:26:25
 7  |  miley |  44  |  54  |  57  |  87  |  2013-01-16 20:26:25
 8  |  john  |  37  |  93  |  59  |  62  |  2013-01-17 20:26:25
 9  |  steve |  85  |  71  |  87  |  74  |  2013-01-17 20:26:25
...etc

*note: this is a random table I made up to just give you an idea of what my table looks like

I need to grab the name for who had the greatest change in a specific column over the course of a specific date range. I've tried a bunch of different queries by can't get one to work. I think my closest solution is something like...

SELECT table1.name, MAX(table1.c1-h.c1) as maxDiff 
FROM table_a as table1
LEFT JOIN table_a as table2 
ON table2.name=table1.name AND table1.c1>table2.c1
WHERE table2.c1 IS NOT NULL

What am I doing wrong? To be clear, I want to be able to select a range of dates then determine who has the biggest difference for that date range in a determined column. Also note that the data only increments over time, so the first capture of any day will always be <= the last capture of the day for that person.

Upvotes: 1

Views: 406

Answers (2)

DRapp
DRapp

Reputation: 48139

It sounds like you will be needing a nested query. First, a query of each person on their own measurements within the date range, then order it by the biggest and take the top 1... something like this may work for you...

select
      PreGroupByName.`Name`,
      PreGroupByName.MaxC1 - PreGroupByName.MinC1 as MaxSpread
   from
      ( select 
              t1.`Name`,
              min( t1.c1 ) as MinC1,
              max( t1.c1 ) as MaxC1
           from
              table_a t1
           where
              t1.`time` between '2013-01-01' and '2013-01-17'  -- or whatever date/time range
           group by
              t1.`Name` ) as PreGroupByName
   order by
      MaxSpread DESC
   limit 1

Upvotes: 2

inhan
inhan

Reputation: 7470

SELECT
    `id`,`name`
    ,MAX(`c1`)-MIN(`c1`) AS `diff_c1`
    -- ,MAX(`c2`)-MIN(`c2`) AS `diff_c2`
    -- ,MAX(`c3`)-MIN(`c3`) AS `diff_c3`
    -- ,MAX(`c4`)-MIN(`c4`) AS `diff_c4`
FROM `the_table`
WHERE `time` BETWEEN '2013-01-13 20:26:25' AND '2013-01-17 20:26:25'
GROUP BY `name`
ORDER BY `diff_c1` DESC -- whichever you want to evaluate
LIMIT 1

Upvotes: 1

Related Questions