MySQL query help

getting the latest of 2 columns

[Seamless]

I have been developing an engineering drawing register on a companies intranet. This enables a user to upload a drawing and enter the drawing details. The drawing itself (PDF) is uploaded to the server and the data entered into the MySQL database.

I have written some php code to do a search on the drawing database e.g. customer search.

The trouble is some customers when issuing a new drawing to the company don't update the revision number only the date of the drawing.

Here's my problem:
I need a MySQL statement to select the drawings with the highest revsion AND the most recent date.

Here's what i have so far.

<?
$search_vals = array("drg_desc", "drg_num", "cust_id");
 while (list ($key, $value) = each ($HTTP_POST_VARS)) {
if (in_array ($key, $search_vals)) {
if (is_numeric($value) and!empty($value)) {
$addtosql .= " $key LIKE '%$value%' AND";
$get_val .= "$key=$value&amp;";
}elseif(!is_numeric($value) and!empty($value)){
$newval = urlencode($value);
$topost .= "&$key=$newval"; //used later in reposting
$value = addslashes($value);
$addtosql .= " $key LIKE '%$value%' AND";
$get_val .= "$key=$value&amp;";
} //fi
} //fi
} //wend
$addtosql = substr("$addtosql", 0, -3);
$sql = "SELECT cust_id, drg_num, drg_desc, max(rev) as rev, drg_date, date, filename FROM drawings WHERE $addtosql GROUP BY drg_num";
?>

There are 3 search fields - drg_desc (Drawing Description), drg_num (Drawing Number) and cust_id (Customer).

The above code will select all drawings with the highest revision according to what is entered into the search fields BUT it if there are 2 drawings with the same details but one has a more recent date, it doesn't neccessarily select the latest drawing.

So to simplify how do i add a max(drg_date) to the SQL statement to make the query select drawings with the highest revision and the most recent date.

[lombie]

In MySQL, if you just want one row it's easy to use:

order by yourcolumn limit 1

Note that ascending order is default, to use descending order use:

order by yourcolumn desc limit 1

In your case you should also include the date field in the group clause and come out with something like:

SELECT cust_id, drg_num, drg_desc, max(rev) as rev, drg_date, date, filename FROM drawings WHERE $addtosql GROUP BY drg_num, drg_date order by drg_date desc limit 1

meaning that the group by clause returns all specific rows (with differing drg_date values) and letting the order by clause filter out all but the latest one