Re: [ALUG] SQL question, I suspect I need some sort of inner join

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