On 14/04/14 13:27, Chris Green wrote:
I think there should be a way to do this but at the moment I can't see how, I always find SQL difficult to fathom though.
I have a table with dates, times and temperatures in it. I want to select the maximum temperature for each year, how do I do that?
The table has columns as follows:- ID Date Time Indoor Temperature Outdoor Temperature
What I want to get out of it is something like:- 2014 21.23 2013 30.45 2012 25.31
It's a sqlite3 database so there's no procedural language easily available. I guess I could write a Python script to do what I want, that might be the easiest way.
I'm quite rusty, but if you were doing this just in SQL, firstly, you need a list of years
SELECT Year(Date) as TheYear from TheTable Order by TheYear Desc Group by TheYear;
There will be some function that returns the year. I don't know exactly what it will be. You want it to return a number rather than a string. If it returns a string, you may run into problems. The "as TheYear" creates a calculated field. Hopefully your dialect of SQL will allow you to sort and group by this field. Order by ...Desc makes the numbers go down. Group by means that you'll get one entry for each unique year.
Actually, revise that, skip the Order by for now. TheTable is the name of your table.
SELECT Year(Date) as TheYear from TheTable Group by TheYear;
If that works... Now you need get the year and temp thusly
SELECT Max([Indoor Temperature]) as MaxInTemp, Year(Date) as TheYear FROM TheTable Where TheYear in { (SELECT Year(Date) as TheYear from TheTable Group by TheYear) } Order By TheYear Desc;
Max is a calculation field which finds the max value of a field (Field is a name for one of your table entries - ID, Date etc) Indoor Temperature is included in [ ] because it includes a space. You may need to use some other escaping to include a field name with a space in it. Others include { }, ", ' or other varieties of '. Best to avoid spaces in field names! Also, Date is likely to be a reserved word of function, so best avoided - use something like SampleDate perhaps. If you don't, you may need to dereference it with something like TheTable.Date.
"In" is a feature not supported by all flavours of SQL. It lets you do stuff like SELECT SampleDate From TheTable where ID in {1, 3, 5} which would return the Sampledate for records 1, 3 and 5. (Select Year(Date)....) will give you an unsorted list of years Finally, sort by TheYear, descending order. (Desc may be descending).
There will be many other ways of doing this. For instance, if using Microsoft Tools to do this in M$ Access or Visual Basic, you could get a list of records (recordset) with the years in it, then run an SQL query to find the max temp for that year, then repeat with the next year etc.
Names of functions, restrictions on field names and language syntax varies between different versions of SQL though, so you'll have to experiment. I hope that the above will give you an idea though.
Steve