enigma1
msg:4211689 | 3:11 pm on Oct 5, 2010 (gmt 0) |
have you tried to append limit 1 at the end of the query?
...WHERE curr.siteid=1 order by curr.date limit 1
|
deejay
msg:4211886 | 8:00 pm on Oct 5, 2010 (gmt 0) |
DOesn't work in this case Enigma1, as I need all matches, not just one, but only the match where the previous date is immediately prior to the current date.
Yeah, I know.. I confuse myself too.
The query example I gave will produce a result with X rows that matches
Current = Previous
2008-12-04 = 2008-11-20
2009-01-12 = 2008-11-20
2009-01-12 = 2008-12-04
2009-02-04 = 2008-11-20
2009-02-04 = 2008-12-04
2009-02-04 = 2009-01-12
The result set I need to get to is:
Current = Previous
2008-12-04 = 2008-11-20
2009-01-12 = 2008-12-04
2009-02-04 = 2009-01-12
.. of course with the correct readings alongside.
Does that help?
|
Demaestro
msg:4211910 | 8:24 pm on Oct 5, 2010 (gmt 0) |
deejay,
The only way I can think of that wouldn't be a nightmare of code would be to put a sequential id into the READINGS table.
If you can get your data like this:
Siteid | Date | Measurement | uniq_sequence_id
1 | 2008-11-20 | 28.432 | 1
1 | 2008-12-04 | 37.681 | 2
1 | 2009-01-12 | 59.529 | 3
1 | 2009-02-04 | 67.831 | 4
Then all you have to do is select the row you want then add 1 to the sequence and grab the row with that sequence id, then subtract 1 from the sequence and grab that row.
SELECT *
FROM readings AS curr
left join readings as prev on (curr.uniq_sequence_id - 1) = prev.uniq_sequence_id
left join readings next on (curr.uniq_sequence_id + 1) = next.uniq_sequence_id
This logic will only work if the sequence doesn't skip any numbers.
|
deejay
msg:4211913 | 8:30 pm on Oct 5, 2010 (gmt 0) |
Shoot.
Thanks DeMaestro. That's going to be awkward at best and probably not reliable - too many other variables in this case.
I might have to accept that it ain't going to fly in mysql alone and get stuck into some php as well.
|
Demaestro
msg:4211949 | 9:11 pm on Oct 5, 2010 (gmt 0) |
Ya, write a php function to deal with the data.
I noticed when I saw the site_id that the sequential thing was most likely too 'hacky' to pull off.
With a simple data set it would do the trick though.
It is on my brain now, usually when something sticks with me like this the answer will come to me while sitting on the toilet or just before/after I sleep.
Basically what you need is a function that gets closest future date and a function that gets closest past date, then you pass it the curr row and let it find those other rows for you then return all three from the function.
|
deejay
msg:4211990 | 11:02 pm on Oct 5, 2010 (gmt 0) |
*lol* well if inspiration strikes, do come back and share please!
It's silly isn't it? This isn't the first time over the years I've had to do just this - mostly in Access though - it seems silly that there isn't a clean way round it.
Ideally maybe a subquery, but I need to be able to delimit the subquery by a field from the top query.
Gaah... would just be so much cleaner if I could do it in the initial query.
|
johnblack
msg:4212003 | 12:01 am on Oct 6, 2010 (gmt 0) |
select measurement as curr_measurement,
(select top 1 measurement
from readings r2
where r2.date < r1.date
and r2.site_id = 1
order by r2.date desc) as prev_measurement
from readings r1
where r1.site_id = 1
That's really off the top of my head and syntactically incorrect, but you get the gist of returning a value from a subquery.
|
deejay
msg:4212028 | 1:28 am on Oct 6, 2010 (gmt 0) |
now that is interesting johnblack. I have to admit, whenever I have to use a subquery my biggest hurdle is deciding just where in the darn top query it goes (select, join, where, etc).
I think top 1 is sql syntax, rather than mysql, but it does raise the option of using limit in the subquery, which might just be what I'm looking for.
I'll go have a play and report back. -thanks.
|
johnblack
msg:4212043 | 2:05 am on Oct 6, 2010 (gmt 0) |
Yeah sorry at my day job at the mo where I use SQL server, but my evening job involves mySQL :)
I'm not even sure if that 'subquery in the select' works in mySQL as I think I've only used it in SQL Server
Sorry for the vagueness but I hope I've pointed you in a helpful direction!
|
deejay
msg:4212044 | 2:21 am on Oct 6, 2010 (gmt 0) |
MORE than helpful johnblack = check out this baby!
SELECT siteid AS curr_siteid, date AS curr_date, measurement AS curr_meas, (
SELECT date
FROM readings AS prev
WHERE siteid =1
AND TYPE =1
AND date < curr_date
ORDER BY date DESC
LIMIT 1
) AS prev_date, (
SELECT measurement
FROM readings AS prev
WHERE siteid =1
AND TYPE =1
AND date < curr_date
ORDER BY date DESC
LIMIT 1
) AS prev_meas, (
SELECT curr_meas - measurement
FROM readings AS prev
WHERE siteid =1
AND TYPE =1
AND date < curr_date
ORDER BY date DESC
LIMIT 1
) AS diff_meas, (
SELECT Datediff( curr_date, date )
FROM readings AS prev
WHERE siteid =1
AND TYPE =1
AND date < curr_date
ORDER BY date DESC
LIMIT 1
) AS diff_date
FROM readings AS curr
WHERE siteid =1
AND TYPE =1
ORDER BY curr_date DESC
Obviously that does a little more than my initial question - it matches each reading to the one with the date immediately prior, and displays the dates and readings, as well as the difference between the two readings and the interval between the dates. Voila:
curr_siteid | curr_date | curr_meas | prev_date | prev_meas | diff_meas | diff_date
1 | 2009-02-04 | 67.831 | 2009-01-12 | 59.529 | 8.302 | 23
1 | 2009-01-12 | 59.529 | 2008-12-04 | 37.681 | 21.848 | 39
1 | 2008-12-04 | 37.681 | 2008-11-20 | 28.432 | 9.249 | 14
1 | 2008-11-20 | 28.432 | NULL | NULL | NULL | NULL
I'm heading towards calculating a growth rate per day, which this all but does at this point.
Hopefully this might help some other weary traveller at some point :)
|
|
