I have a sqlite table of temperatures with columns as follows:-
id, Date, Time, Inside_Temperature, Outside_Temperature
How do I get the date and time for the minimum and maximum temperatures for a specific year?
It's easy enough to get the required temperature:-
SELECT min(Inside_Temperature) from temperatures;
... and even to get the Date and Time associated with that:-
SELECT Date, Time FROM temperatures WHERE Inside_Temperature = (SELECT min(Inside_Temperature) FROM temperatures);
However if I want to find the minimum temperature in 2012 I'm a bit stuck.
SELECT Date, Time FROM temperatures WHERE Inside_Temperature = (SELECT min(Inside_Temperature) FROM temperatures WHERE Date like '2012%');
... returns dates in other years where the temperature happened to be the same. I can make the outer SELECT only look in 2012 but that's beginning to get a bit messy.
Is there an easier/better way where I join using the id which matches the minimum temperature to select the other columns I want?