On 20 Feb 13:24, Chris Green wrote:
On Mon, Feb 20, 2012 at 01:11:11PM +0000, Chris Green wrote:
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?
Well I puzzled my way to *a* solution:-
select Date, Time, min(Outside_Temperature), substr(Date, 1, 4) AS year FROM temperatures WHERE year='2012' GROUP BY year;
So, part the first:
test=# select * from temps where id in (select id from temps where extract(year from date) = 2012 order by in_temp asc,date asc limit 1); id | date | time | in_temp | out_temp ----+------------+----------+---------+---------- 65 | 2012-04-16 | 18:09:00 | -29 | -16 (1 row)
That's the minimum temperature in my evilly created sample data for the year 2012.
Taking out the qualification for the year gives:
test=# select * from temps where id in (select id from temps order by in_temp asc,date asc limit 1); id | date | time | in_temp | out_temp ----+------------+----------+---------+---------- 82 | 2011-11-02 | 03:24:00 | -30 | 17 (1 row)
Max and minimum at the same time:
test=# select * from temps where id in ((select id from temps where extract(year from date) = 2012 order by in_temp asc,date asc limit 1) union (select id from temps where extract(year from date) = 2012 order by in_temp desc,date asc limit 1)); id | date | time | in_temp | out_temp ----+------------+----------+---------+---------- 65 | 2012-04-16 | 18:09:00 | -29 | -16 87 | 2012-05-28 | 19:19:00 | 29 | 5 (2 rows)
I'm sure from there you can work out the rest. This is using postgresql, though - I haven't tested in in sqlite, but it *should* work there too.
Note that I'm using in_temp for those tests, the same with out_temp is:
test=# select * from temps where id in ((select id from temps where extract(year from date) = 2012 order by out_temp asc,date asc limit 1) union (select id from temps where extract(year from date) = 2012 order by out_temp desc,date asc limit 1)); id | date | time | in_temp | out_temp ----+------------+----------+---------+---------- 86 | 2012-05-30 | 07:01:00 | -3 | -30 94 | 2012-07-02 | 12:02:00 | -23 | 29
Hope that helps,