Getting the most often date

[Anon]

I currently have a table that looks like this.

1 2000-07-20
2 2000-07-20
3 2000-07-20
4 2000-07-20
5 2000-07-20
6 2000-07-21
7 2000-07-21
8 2000-07-22
9 2000-07-22
10 2000-07-22

The first column is a auto_increment integer named, ID.
The second column is a date.

I want to do a search to find what date has been listed the most times. In this case it would be 2000-07-20 with 5.

Any help on the syntax of this SQL statement would be appreciated.

Thanks

[Anon]

Jared,

I'm not sure how you'd select what you require in only the sql statement, but you could try this:

<?
$host = 'your_host_name';
$user = 'your_user_name';
$pass = 'your_password';
$db = 'your_db_name';
$table = 'your_table_name';

mysql_connect($host, $user, $pass) or die("Cannot logon to server");
mysql_select_db($db) or die("Cannot select database");

$sql = 'select date, count(date) as count from $table group by date';

$result = mysql_query($sql) or die("Unable to perform query");
$return_rows = mysql_fetch_array($result) or die("Unable to cenvert results to usable format");

$max_count = 0;
$max_date = '';

do
	{
		echo "Date: $return_rows[date]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Count: $return_rows[count]<br>";

		if ($max_count < $return_rows[count]) 
			{
				$max_count = $return_rows[count];
				$max_date = $return_rows[date];
			}
	}

while ($return_rows = mysql_fetch_array($result));

echo "<br>With a count of $max_count, the date $max_date has the greatest number of entries in this table."; 

?>

This script makes the assumption that no date will have the same number of entries, but it shouldn't be too hard to hack it a bit more to allow for this. Hope this helps!!!

Cheers,

Geoff

[Anon]

Use this:

SELECT date, COUNT(date) AS number FROM my_table GROUP BY date ORDER BY number DESC

You'd get a result like this:

+------------+-------+
| date &nbsp; &nbsp; &nbsp; | count |
| 2000-07-20 | 5 &nbsp; &nbsp;|
| 2000-07-22 | 3 &nbsp; &nbsp;|
| 2000-07-21 | 1 &nbsp; &nbsp;|
+------------+-------+

You could also append LIMIT 0,1 to the SQL query to get the top row only.

Greetz,

Vincent Driessen
Venlo, The Netherlands