Retrieving second last record from a mysql table

Question

I have a table that holds readings of various meters

+-----------+---------+---------+---------+---------------------+
| idReading | idMeter |  index  |  usage  | dateOfReading       |
+-----------+---------+---------+---------+---------------------+
|       671 |     189 | 884.000 | 441.000 | 2012-06-01 00:00:00 |
|       672 |     190 | 170.000 |  82.000 | 2012-06-01 00:00:00 |
|       584 |     190 |  88.000 |  88.000 | 2012-05-01 00:00:00 |
|       583 |     189 | 443.000 | 443.000 | 2012-05-01 00:00:00 |
+-----------+---------+---------+---------+---------------------+

In my application I need to have available both the current reading (which may or may not be present) and the previous reading which may be from a few months back. So I may have the present reading in July, but the previous reading could be in April.

I am using the following query to retrieve the previous reading:

SELECT * FROM (
  SELECT Reading.idMeter, Reading.usage, Reading.`Index`, Reading.dateOfReading
  FROM meterReadings AS Reading
  WHERE Reading.idMeter IN (189,190)
    AND Reading.dateOfReading < '2012-07-31'  
  ORDER BY Reading.dateOfReading DESC) AS OrderendReading
GROUP BY OrderendReading.idMeter

This works decent, but I am not happy that I am using a SELECT within another SELECT as I fear as the table gets bigger and bigger the request will take longer and longer. Is there any other way to retrieve the previous reading? Or is there any way to make this query more efficient?

Or, ideally, have both the present reading and the previous one in the same query?

Thank you.

have you tried using [`LIMIT`](http://dev.mysql.com/doc/refman/5.1/en/select.html)? — Jason, Jun 17 '12 at 18:45
I have tried just the inner query without the ordering and using the group by, but it only gave me result from May, not even June, so I can't see how `LIMIT` might help. — eXtenZy, Jun 17 '12 at 18:48
use `LIMIT` on the outer query. You just want the first two results, right? — Jason, Jun 17 '12 at 19:16
I need the readings of any number of meters, as specified in the `IN` clause, but my table is not ordered by dateOfReading as in the example, it is ordered by the idReading column. — eXtenZy, Jun 17 '12 at 19:26

score 1 · Answer 1 · answered Jun 17 '12 at 20:14

I think a correlated subquery would achieve what you are after:

SELECT  Reading.idMeter, 
        Reading.usage, 
        Reading.`Index`, 
        Reading.dateOfReading,
        (   SELECT  mr.Usage
            FROM    MeterReadings AS mr
            WHERE   mr.idMeter = Reading.idMeter
            AND     mr.DateOfReading < Reading.DateofReading
            ORDER BY DateOfReading DESC
            LIMIT 1
        ) AS PreviousReadingUsage
FROM    meterReadings AS Reading,
WHERE   Reading.idMeter IN (189,190)
AND     Reading.dateOfReading < '2012-07-31'  
ORDER BY Reading.dateOfReading DESC

score 0 · Answer 2 · answered Jun 17 '12 at 18:52

0

I just discovered 'Analytic functions' yesterday, and I'm pretty sure they cover this type of requirement. Give it a google, some tutorials on Youtube too.

answered Jun 17 '12 at 18:52

Tams

182
1
1
8

In fact there's an example on here: http://stackoverflow.com/questions/2491063/predefined-function-or-method-available-to-get-second-highest-salary-from-an-emp – Tams Jun 17 '12 at 19:18
This is more of a comment than an answer. Also these are not available in MySQL (Yet). Using an incrementing variable as used in a [recent answer of mine](stackoverflow.com/questions/10980957/#10983910) is the only way I know of to emulate the `ROW_NUMBER()` functions of other DBMS. – GarethD Jun 17 '12 at 20:19
Yes, I tried to comment, but I don't seem to have that option unless there's an answer. Any ideas why that might be? Is it a restriction for new members? I just joined yesterday. – Tams Jun 17 '12 at 22:20
Yes. It requires a reputation of 50 I think. – GarethD Jun 17 '12 at 22:40

score 0 · Answer 3 · edited Jul 16 '12 at 08:15

0

Try this:

SELECT 
  Reading.idMeter, 
  Reading.usage, 
  Reading.`Index`,
  (Select dateOfReading from meterReading order by dateOfReading desc limit 1,1)  
FROM meterReadings AS Reading   
GROUP BY Reading.idMeter;

edited Jul 16 '12 at 08:15

bluish

26,356
27
122
180

answered Jun 17 '12 at 19:08

Sashi Kant

13,277
9
44
71

That gives me the reading only from one meter. I need the readings of all the meters in the `IN` clause. – eXtenZy Jun 17 '12 at 19:23
Yes, for each meter I specify in the `IN` clause. – eXtenZy Jun 17 '12 at 19:30

Retrieving second last record from a mysql table

3 Answers3