SQL Error?

Anon

This works, but I am not getting the correct result. Any idea why?

$sql = SELECT max(month), max(year) FROM table WHERE country LIKE '$country';

when I execute this, it comes up with 9/2000
however, the max month should be 10 (and when I look in the database, the max month IS 10 and max year 2000). I did not set up the database, so I can't change it to keep the dates in one field.

Anon

Are you sure that max month is 10 and max year is 2000 FOR country like '$country' ?

can you please give the example of the real data in the database.

what mysql version you are use ?

Anon

yes, if do a "select month,year from table where country like 'afghanistan'; (the country i am trying to get) i get 77 records
the last one having month 10 year 2000. the one before that is month 8 year 2000. so for some reason it is getting the next to last entry.

Actually I just tried the following query, and it gets the correct value. Is there any reason I should not go about it this way?

$sql = "SELECT month, year from table where country like '$country' group by month order by year desc limit 1";

Anon

actually it works for Afghanistan, but if run the same query (select month, year from table where country like '$country' group by month order by year desc limit1😉 for United States, it comes up the last entry being in 1945...but if i do a select month,year from table where country like '$country';, and look at all the dates, the last date is in 2000. What is going on here?

Anon

Oops,

I am not clear understand what you want.
Do you want to receive the max date from one row ? Or you want to receive the max month and max year which can be in the different rows.

For example what you want to receive in this case:

month year country
10

Anon

Sorry 🙂

....

For example what you want to receive in this case:

month year country
10 2000 Russia
9 2001 Russia

is it must be 10/2001 (different rows)
or
9/2001 (real max date from one row)

in fact this query:

select max(mon),max(year) from dates where country like 'Russia';

return 10/2001 i.e. maximum value in each column. And these values are from different rows.

Anon

i have a table that has

Month, Year, Country, Disaster

8, 2001, afghanistan, drought
7, 2001, afghanistan, drought
7, 2000, afghanistan, flood
5, 2001, united states, flood
10, 2001, afghanistan, earthquake
4, 1980, france, flood
6, 1954, afghanistan, drought

etc.

I need to find the most recent date and the oldest date for each country. so with my example dataset I have it printing out something like this

$country Disaster History: From $lastdate To: $firstdate

Afghanistan Disaster History: From 6/1954 To: 10/2001

does that make sense now? I have hundreds of records to check.

I guess that's why I don't understand why select max(month) max(year) from table where country like '$country' group by year order by year desc wouldn't work.

Anon

If you really want to receive maximum date, then one of the way which is working is

select max(year*100+month) from dates where country like 'Russia';

in this case you will receive as a query result something like this:
200109

Of course you need to process this number in the script to get year and month.

This is not very good solution of course, but it working. I am sure that it is possible to find something better.

🙂)

Anon

this is working too, I hope 🙂)

select month, year from dates where country like 'Russia' order by year desc, mon desc limit 1;

hope this help 🙂)