Displaying the latest submitted date

[HoboTraveler]

Hi All,

I have a MySQL database with one table containing users who submit info and this is tagged to the country they're from.

The query below, will list a table GROUPED according to the country and displaying the number of entries in each country.

Eg:
Austria - 2
Australia - 10

I am looking into the possibility of displaying the latest date from the table. For example: Two users in Australia submitted entries on 1 October and 10th October. So the "Last Date Submitted" should show, 10th October under Australia.

$list_countries = mysql_query("SELECT country, date_format(date, '%M %d, %Y') AS date, count(country) AS num_of_entries FROM table GROUP BY country")

Btw, each entry has its uniqueID too. How do I display the latest date when GROUPING?

TIA

[jackvull]

Try max(date) in the select statement

[infpack]

Add this to the end of your query:

order by date desc

Regards
<snip>

[edited by: engine at 2:05 pm (utc) on Nov. 11, 2005]
[edit reason] No sigs, see TOS [/edit]

[HoboTraveler]

That did not work. I dont think a SORT will work in a case like this.

Heres how the table should be displayed:

---------------------------------------------------
Country ¦ Number of Submissions ¦ Date Last Updated
---------------------------------------------------

Australia ¦ 50 ¦ October 10
Austria ¦ 20 ¦ March 21
Argentina ¦ 60 ¦ November 15

The "Date Last Updated" needs to show the date of the last submitted submission in the specific country.

Any ideas?

TIA

[HoboTraveler]

jackvull,

Thanks for the max(date). Works great!

mysql_query ("SELECT *, date_format(date, '%M %d, %Y') AS date FROM table WHERE test = '$test'")

[HoboTraveler]

Oops! Sorry for the incorrect query above..Here is the correct one:

("SELECT ID, country, count(type) AS type1, date_format(max(date), '%M %d, %Y') AS date FROM table WHERE test='$test'")