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