Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Inner Join the same table to compare record against the previous record

Posted on 2004-08-26
3
Medium Priority
?
403 Views
Last Modified: 2008-02-01
I am working with readings from a meter (like an electric meter), where the readings are on a totalizer, so to get the amount used between readings, you subtract the earlier reading from the later reading.

So, I am trying to do a sql statement that joins in the immediate previous reading so you can subtract it.

The table (meter_data) with the essential fields and example data...

reading_id   read_date     read_time     reading
-----------    -----------      -----------      ---------
    1            2004-08-25    4:00:00         100
    2            2004-08-26    5:00:00         150
    3            2004-08-26    11:00:00       170

Right now, I have something like...

SELECT m1.read_date,m1.read_time,m1.reading - m2.reading as amount_used

FROM meter_data m1 inner join meter_data m2 on mm1.read_date >= mm2.read_date and mm1.read_time > mm2.read_time

This gets the records setup, but it obviously doesn't limit it to the "immediate previous reading", and will get any reading previously, so I end up with more records than I want.

The results I would be looking for would be...

reading_id   read_date     read_time     amount_used
-----------    -----------      -----------      ---------
    3            2004-08-26    11:00:00         20
    2            2004-08-26    5:00:00          50

This seems like it shouldn't be hard, but my skills are slow today...

Thanks.

0
Comment
Question by:keeper3
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 500 total points
ID: 11906737
If you tack on
GROUP BY m2.read_date, m2.read_time
it seems to work, although you'll probably want to check me on that.
0
 

Author Comment

by:keeper3
ID: 11906875
Thought it was easy, many thanks.
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 11906876
On further study, that doesn't seem to work so well.  If reading_id is always increment by 1, you could join on m1.reading_id = m2.reading_id + 1, but I don't much like that solution.  

How about this:

SELECT
    m1.read_date, m1.read_time, m1.reading - m2.reading as amount_used
FROM
    meter m1, meter m2
WHERE
    (m1.read_date > m2.read_date OR (m1.read_date = m2.read_date AND m1.read_time > m2.read_time))
GROUP BY
    m2.read_date, m2.read_time;
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question