Help using MIN and Groups

[perficut]

This is probably a simple task but I cant seem to get it. I've tried a hundred different configs.

 

First thing I need to do is get is all the data in the record containing the solicitation# and where ContractorID is a particular value.  Then I need to figure out how to get the data in the record that contains the lowest value in the ROADS field.

 

Table Example

    -------------------------------------------------------

    |Solicitation|ContractorID|  Roads  | Walkways | Patios |

    -------------------------------------------------------

1  |255290    |910            | 19.00  |  25.00    | 30.00  |

2  |255290    |910            | 12.00  |  12.00    | 14.00  |

3  |255290    |850            | 8.000  |  21.00    | 12.00  |

4  |255290    |850            | 11.00  |  14.00    | 26.00  |

    -------------------------------------------------------

 

I want to be able to grab all the data in row#2 where the ContractorID(910) has bid the lowest amout in the Roads Column for this solicitation. Then display it to the screen so he/she can see what their lowest offer was.

 

heres where im at.  It seams to only want to return the first row.

 

$sql="SELECT * , MIN(Roads) FROM ProcurementBids WHERE Solicitation='$property' AND ContractorID='{$contractor_id}' " or die(mysql_error());  ;
$result=mysql_query($sql);
//$row = mysql_fetch_array( $result );
$lowestbidroads = $row['Roads'];
$lowestbidwalkways = $row['Walkways'];
$lowestbidasphaltdeice = $row['AsphaltDeice'];
$lowestbidwalkwaydeice = $row['WalkwayDeice'];
while($row = mysql_fetch_array($result)){
echo $row['Solicitation']. " - ". $row['Roads']. " - ". $row['Walkways']. " - ". $row['AsphaltDeice']." - ". $row['ContractorID'];
echo "<br />";
}

[kickstart]

Hi

 

MIN will give you the lowest value, but you SQL won't necessarily link that lowest value to the row the other columns are brought back from.

 

Something like this would do it:-

 

SELECT *
FROM ProcurementBids a
JOIN (SELECT Solicitation, ContractorID, MIN(Roads) AS minRoads FROM ProcurementBids WHERE Solicitation='$property' AND ContractorID='{$contractor_id}') b
ON a.Solicitation = b.Solicitation
AND a.ContractorID = b.ContractorID
AND a.roads = b.minRoads

 

However it will bring back 2 rows if the are 2 Solicitations for a contractor id with the same min roads but other fields different.

 

All the best

 

Keith

[perficut]

Thanks Ill give that a try. Thanks.

[perficut]

I would think would have been an easier way.

What if you grouped all the rows by the solicitation number and then picked out the row by the contractor which that had the lowest Roads value?