0

I have a MySQL table containing benchmarks like so..

benchmarkid     playerid         verified     benchmark  resultnumber   testdate  

1               70               1            40msprint  6.0            2013-06-03      
2               70               1            40msprint  6.1            2013-06-04      
3               70               1            40msprint  6.3            2013-06-05  
4               71               1            40msprint  6.0            2013-06-03      
5               71               1            40msprint  6.1            2013-06-04      
6               71               1            40msprint  6.3            2013-06-05

I would like to query that table with a date for the benchmark "40msprint" to get the current result for that date and the last known result at that time for each player.

Eg at 2013-06-04 for verified=1 40msprint for players 70 and 71 it would look like.

playerid    currentresult       previousresult  previousdate        
70          6.1                     6.0         2013-06-03      
71          6.1                     6.0         2013-06-03

I will also know the playerid's i want to find and there will only be about 20 at a time.

Any ideas for a MySql query that would get me this?

Brendon Gearin
  • 3
  • 1
  • 1
    [What you have tried?](http://mattgemmell.com/2008/12/08/what-have-you-tried/) – Jason McCreary Jul 29 '13 at 15:30
  • possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – Marc B Jul 29 '13 at 15:50

1 Answers1

0

Given this test data:

CREATE TABLE Table1
    (`benchmarkid` int, `playerid` int, `verified` int, `benchmark` varchar(9), `resultnumber` decimal(5,2), `testdate` date)
;

INSERT INTO Table1
    (`benchmarkid`, `playerid`, `verified`, `benchmark`, `resultnumber`, `testdate`)
VALUES
    (1, 70, 1, '40msprint', 6.0, '2013-06-03'),
    (2, 70, 1, '40msprint', 6.1, '2013-06-04'),
    (3, 70, 1, '40msprint', 6.3, '2013-06-05'),
    (4, 71, 1, '40msprint', 6.0, '2013-06-03'),
    (5, 71, 1, '40msprint', 6.1, '2013-06-04'),
    (6, 71, 1, '40msprint', 6.3, '2013-06-05'),
    (7, 70, 1, '40msprint', 6.1, '2013-06-06'),
    (8, 71, 1, '40msprint', 6.1, '2013-06-06'),

    (9, 71, 1, '40msprint', 6.3, '2013-06-02'),
    (10, 70, 1, '40msprint', 6.1, '2013-06-02')
;

this query works:

select
t1.testdate as date,
t1.playerid,
t1.resultnumber,
t2.testdate as previous_date,
t2.resultnumber as previous_result
from
Table1 t1
left join Table1 t2 on t1.testdate > t2.testdate and t1.playerid = t2.playerid
where
t1.testdate = '2013-06-05'
and t2.testdate = (SELECT MAX(testdate) FROM Table1 WHERE Table1.testdate < t1.testdate)

Result:

|                        DATE | PLAYERID | RESULTNUMBER |               PREVIOUS_DATE | PREVIOUS_RESULT |
---------------------------------------------------------------------------------------------------------
| June, 05 2013 00:00:00+0000 |       70 |          6.3 | June, 04 2013 00:00:00+0000 |             6.1 |
| June, 05 2013 00:00:00+0000 |       71 |          6.3 | June, 04 2013 00:00:00+0000 |             6.1 |
fancyPants
  • 50,732
  • 33
  • 89
  • 96