Finding newest record from query results

[marky7890]

Hi,

 

I'm having trouble working out how to do the following:

 

So I have a weather station and the extremes for each day are stored in a MySQL database. I'm planning on having a page showing the 10 highest and lowest temperatures, wind guests, etc.

 

This uses the query:

 

SELECT Date, TempMax FROM wx_daily ORDER BY TempMax DESC LIMIT 0 , 10

 

This will output something like this (example data):

 

2009/06/17 | 22.3

2010/05/30 | 22.0

2011/08/06 | 21.7

2010/07/04 | 21.4

2009/06/14 | 21.0

2011/05/24 | 20.0

2010/08/16 | 19.8

2011/05/15 | 19.6

2009/08/02 | 19.1

2010/06/09 | 18.7

 

I now want to find the newest record from the above results (2011/08/06), this is where I got stuck. Any Ideas on how to do this?

 

Thanks,

Mark

 

 

[The Little Guy]

first convert your dates to real mysql dates (YYYY-MM-DD).

 

second, your query would then look like this:

 

SELECT Date, TempMax FROM wx_daily ORDER BY TempMax DESC, Date DESC LIMIT 10

 

This probably won't work for the dates format you have.

[The Little Guy]

Come to think of it, I would create a temporary table (mostly because I like them)

 

The query would looks something like this:

create temporary table temp_dates (the_date, temp);
insert into temp_dates (the_date, temp) select Date, TempMax FROM wx_daily ORDER BY TempMax DESC LIMIT 10;
select * from temp_dates order by the_date desc limit 1;

[fenway]

A temporary table just to sort????

[marky7890]

The date field is already in MySQL Date format, I just showed it wrong in my example.

 

I tried your ideas but they just showed the ordered list of the 10 highest temperatures.

 

 

[fenway]

Then you need to wrap this is yet another SELECT.

[The Little Guy]

Then you need to wrap this is yet another SELECT.

 

or a temporary table

[ebmigue]

Change:

SELECT Date, TempMax FROM wx_daily ORDER BY TempMax DESC LIMIT 0 , 10

 

To:

SELECT Date, TempMax FROM wx_daily ORDER BY Date DESC LIMIT 1

[The Little Guy]

That won't get the newest record from the returned result set, that will get the newest record from the table.

[The Little Guy]

fenway's method:

 

select Date, TempMax from (SELECT Date, TempMax FROM wx_daily LIMIT 10) as temps order by temps.Date desc limit 1

[marky7890]

fenway's method:

 

select Date, TempMax from (SELECT Date, TempMax FROM wx_daily LIMIT 10) as temps order by temps.Date desc limit 1

 

This almost worked just needed to add an order tag for the second select:

 

select Date, TempMax from (SELECT Date, TempMax FROM wx_daily ORDER BY TempMax Desc LIMIT 10) as temps order by temps.Date desc limit 1 

 

Thanks for all your help everyone.