MySQL Query selection

Question

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/>";
}

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

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

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

So the output should be:

1-20131108V2
2-20131108V1

willy · Accepted Answer · 2013-11-08 02:48:54Z

1

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.

answered Nov 8, 2013 at 2:48

willy

1,50011 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NewPHP Over a year ago

huh! that's correct. Thanks! I have been thinking other ways..you saved my day :)

Collectives™ on Stack Overflow

MySQL Query selection

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related