Mysql query to get 2 most recent for a given item number

Question

I have the following attributes in my DB.

statistic id, devicename, value, timestamp.

For a given statistic, I want to find the 2 most recent timestamps and corresopnding values for a every unique device.

I am trying stuff like

Trial 1)

select statistic, devicename, value, timestamp
from X_STATSVALUE
where statistic=19
order by orgtime DESC limit 2;

This gives me the top 2 timestamps, but not per device.

Trial 2)

select statistic, devicename, value, timestamp
from X_STATSVALUE as x 
where x.statistic=241
  and (select count(*)
       from X_STATSVALUE as y
       where y.statistic=x.statistic
         and y.device=x.device
         and y.timestamp > x.timestamp) <=1;

But that's not working too well either..

Basically, I want the 2 most recent timestamps with values for each device, for a given statistic.. any help is really appreciated :)

Answer 1

This is how I solve this type of problem:

SELECT x.statistic, x.devicename, x.value, x.timestamp
FROM X_STATSVALUE AS x
  LEFT OUTER JOIN X_STATSVALUE AS x2
    ON (x.statistic = x2.statistic 
    AND x.devicename = x2.devicename 
    AND x.timestamp < x2.timestamp)
GROUP BY x.statistic, x.devicename
HAVING COUNT(*) < 2;

In other words, show the rows such that there are fewer than two other rows with the same statistic and devicename and a greater (more recent) timestamp.

I'm assuming that you won't have duplicates in the timestamp column for a given statistic & devicename.

Answer 2

Try something like :

SELECT DISTINCT x.statistic, x.devicename, x.value, x.timestamp
FROM X_STATSVALUE AS x
WHERE x.timestamp IN (SELECT timestamp
                      FROM X_STATSVALUE
                      WHERE devicename = x.devicename AND statistic = x.statistic
                      ORDER BY timestamp LIMIT 2)

(may not work in old MySQL though)

Answer 3

I tested the following query on my system wid a demo table like yours and it works. I have considered orgtime instead of timestamp.. you can do the same (just name change required)

    select t1.statistic, devicename, value, timestamp from X_STATSVALUE as t1 

    inner join 

    ( select statistic, max(orgtime) as orgtime from X_STATSVALUE group by statistic ) 
as t2 

    on t1.statistic = t2.statistic and t1.orgtime = t2.orgtime

    UNION

    select tb1.statistic, tb1.devicename, tb1.value, tb1.timestamp
    from X_STATSVALUE as tb1 inner join 

    ( select statistic, max(orgtime) as orgtime from X_STATSVALUE  WHERE statistic+orgtime not in 
    (select t1.statistic+t1.orgtime from X_STATSVALUE as t1 inner join 
    ( select statistic, max(orgtime) as orgtime from X_STATSVALUE group by statistic ) 
as t2 on t1.statistic = t2.statistic and t1.orgtime = t2.orgtime
    ) group by statistic 
    ) 

    as tb2 on tb1.statistic = tb2.statistic and tb1.orgtime = tb2.orgtime

Answer 4

Thanks for all your responses, unfortunately none of the queries returned below give me a good response time when I have say 6000 records in my table.. so basically I am looking for something like this.. any ideas on how to do it.?

mysql> select stats, item, value, orgtime from X_STATSVALUE where stats=19 and item="1/sfx/rs2/20" order by orgtime limit 2;

+-------+--------------+-------+---------------+

| stats | item | value | orgtime |

+-------+--------------+-------+---------------+

| 19 | 1/sfx/rs2/20 | 2 | 1228845663924 |

| 19 | 1/sfx/rs2/20 | 2 | 1228845963935 |

+-------+--------------+-------+---------------+

2 rows in set (0.03 sec)

just that in the above table, the item name would come from the following query:

select distinct item from X_STATSVALUE;

how can I direct the output of one query to the other ? sorry.. am a novice on db queries.. just trying to figure this one out..thanks a lot.