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

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.