SQL question, I suspect I need some sort of inner join
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? -- Chris Green
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; I'm not at all sure whether it's the most efficient or neatest solution though so any other offers would still be very welcome. -- Chris Green
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, -- Brett Parker
On 20 Feb 13:55, Brett Parker wrote:
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
Or better: test=# select min_out_temp_date,min_out_temp,max_out_temp_date,max_out_temp from ((select 1 as id,date + time as min_out_temp_date,out_temp as min_out_temp from temps where extract(year from date) = 2012 order by out_temp asc,date asc limit 1) as min left join (select 1 as id,date + time as max_out_temp_date,out_temp as max_out_temp from temps where extract(year from date) = 2012 order by out_temp desc,date asc limit 1) as max on min.id=max.id); min_out_temp_date | min_out_temp | max_out_temp_date | max_out_temp ---------------------+--------------+---------------------+-------------- 2012-05-30 07:01:00 | -30 | 2012-07-02 12:02:00 | 29 (1 row) Which gives you what you want in one result set... It gives the first date for the min and max temps, and joins the date with the time. -- Brett Parker
On Mon, Feb 20, 2012 at 01:24:59PM +0000, 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;
I'm not at all sure whether it's the most efficient or neatest solution though so any other offers would still be very welcome.
... but it's wrong, the Date and Time are simply the last ones for the specified year. :-( -- Chris Green
On 20/02/12 13:11, Chris Green wrote:
Is there an easier/better way where I join using the id which matches the minimum temperature to select the other columns I want?
Not sure this is "better" but how about: SELECT Date, Time FROM temperatures WHERE Date like '2012%' ORDER BY Inside_Temperature ASC LIMIT 1 Would only get you one result (not one for each date that the minimum occurred on) which may be a deal-breaker? -- Mark Rogers // More Solutions Ltd (Peterborough Office) // 0844 251 1450 Registered in England (0456 0902) 21 Drakes Mews, Milton Keynes, MK8 0ER
On Mon, Feb 20, 2012 at 02:21:00PM +0000, Mark Rogers wrote:
On 20/02/12 13:11, Chris Green wrote:
Is there an easier/better way where I join using the id which matches the minimum temperature to select the other columns I want?
Not sure this is "better" but how about:
SELECT Date, Time FROM temperatures WHERE Date like '2012%' ORDER BY Inside_Temperature ASC LIMIT 1
Would only get you one result (not one for each date that the minimum occurred on) which may be a deal-breaker?
Now that's what I call lateral thinking! :-) I just want the minimum temperature, I'm not too fussed if it occurs on more than one date, so the above looks good. -- Chris Green
On 20 February 2012 15:41, Chris Green <cl@isbd.net> wrote:
On Mon, Feb 20, 2012 at 02:21:00PM +0000, Mark Rogers wrote:
Not sure this is "better" but how about:
SELECT Date, Time FROM temperatures WHERE Date like '2012%' ORDER BY Inside_Temperature ASC LIMIT 1
Now that's what I call lateral thinking! :-)
I've heard it called worse :-)
I just want the minimum temperature, I'm not too fussed if it occurs on more than one date, so the above looks good.
Glad to be of assistance. Going a step further though, depending on the size of the dataset I don't think that "Date like '2012%'" is the fastest way to pull the year out of the date field, unless Date is actually stored as [var]char? Otherwise I would be surprised if MySQL can use its index against that (and this sounds like the kind of table with lots of records in it?). There are other ways to get the year out of a date but you may have to resort to "Date BETWEEN '2012-01-01' AND '2012-12-31'" (not sure how the edge cases are covered, you might need to add/subtract a day each end). But prepending EXPLAIN to the query will tell you whether it's using a suitable index or not. Mark -- Mark Rogers // More Solutions Ltd (Peterborough Office) // 0844 251 1450 Registered in England (0456 0902) @ 13 Clarke Rd, Milton Keynes, MK1 1LG
participants (3)
-
Brett Parker -
Chris Green -
Mark Rogers