February 17, 2009 at 2:41 pm
Ok, new to SQL Server so bear with me..
I have a simple view with the following columns which is queried out of a larger table. It limits itself to only pulling the last 45 minutes of data.
- station_id, time_tag, sensorname, and ed_value
Data is collected every 15 mins, so there should be (and is) 3 records / station in the above query. That's fine.. works ok. Problem is I don't want to show all 3 records for each station, and instead want to see only the MAX time_tag value (and associated ed_value) for each station. If I try (oh lord I have tried!) to limit the above query to pull just 15 mins, I get NO results for stations which are slighlty behind in the polling sequence. I can't not have them there, even if they are a little older! If I bump it to 18 minutes (to catch those lagging stations) I get 2 records for other stations!
Coming from my MSAccess roots this seemed pretty simple, but has cost me a good day of hair pulling.
Not sure what info I could attach that would help you folks better understand this.. Any help would be welcome!
February 17, 2009 at 2:50 pm
Try this:
SELECT station_id, MAX(time_tag) AS time_lag, sensorname, ed_value
FROM NameOfView
GROUP BY station_id, sensorname, ed_value
Greg
February 17, 2009 at 2:57 pm
jduncan (2/17/2009)
Ok, new to SQL Server so bear with me.....
Coming from my MSAccess roots this seemed pretty simple, but has cost me a good day of hair pulling.
Not sure what info I could attach that would help you folks better understand this.. Any help would be welcome!
To answer the above, read you should the first article linked below in my signature block.
February 17, 2009 at 3:02 pm
Thanks for the quick reply / suggestion, but I tried that (hence me thinking this was a simple MAX query!?!), and since the ed_value is different for each time tag it still shows multiple rows per station..
In trying to explain it clearly, I think I actually came up with a solution...
View 1 - Finds the MAX time tag for all stations (station_id, time_tag, sensorname)
View 2 - Joins View 1 and my data table to pull the matching ed_values! (ed_value)
Just gotta test it now..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply