MySQL Query selection

I have a big database table called pricing. The fields are following:

id code description rrp buy stock date distributor vendor version

User select the vendor field only. version field can be vary for each distributor. I need to display all the details from pricing table but only the recent version of each distributor. For that I am trying to get all distributors and it’s recent version. I have coded as below:

$all_distributors=mysql_query("SELECT `distributor`,MAX(`version`) as `version` FROM `pricing` WHERE `vendor`='$vendor_id' GROUP BY `distributor`,`version`",$con);
while($all_dist= mysql_fetch_array($all_distributors))
{
    $d_ID=$all_dist['distributor'];
    $d_version=$all_dist['version'];
    echo $d_ID."-".$d_version."<br/>";
}

​x
 
$all_distributors=mysql_query("SELECT `distributor`,MAX(`version`) as `version` FROM `pricing` WHERE `vendor`='$vendor_id' GROUP BY `distributor`,`version`",$con);while($all_dist= mysql_fetch_array($all_distributors)){    $d_ID=$all_dist['distributor'];    $d_version=$all_dist['version'];    echo $d_ID."-".$d_version."<br/>";}​

But the output shows all versions of distributors, like below:

distributor-version
1-20131107V1
1-20131108V2
2-20131108V1

 
distributor-version1-20131107V11-20131108V22-20131108V1​

How can I display only the latest version of each distributor?

So the output should be:

1-20131108V2
2-20131108V1

 
1-20131108V22-20131108V1​

Answer

Your grouping by distributor, version makes it so that any unique combo of distributor and version is a separate group. This is what is being displayed in your output. I think you only want to group by distributor if I’m reading the question right.

Advertisement

Answer