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,

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. :-(

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.