I commented out the LIMIT statement because it’s not working. All selected rows are always returned, no matter what.

Code:

 $  sql = "SELECT riderUid AS  `rider`, ( ";  $  sql .= "SELECT SUM( mileage ) ";  $  sql .= "FROM  `mileage` ";   if (isset($  ytd) && $  ytd == 1) {      $  sql .= "WHERE riderUid =  `rider` AND $  year = YEAR(CURDATE()) ";      $  title = "Year-to-date";  } else {      $  sql .= "WHERE riderUid = `rider` AND YEAR(rideDate) = YEAR(CURDATE()) AND MONTH(rideDate) = MONTH(CURDATE()) ";      $  title = "This month";  }   $  sql .= "ORDER BY mileage DESC ";  //$  sql .= "LIMIT 5 ";  $  sql .= ") AS  `miles` ";  $  sql .= "FROM  `mileage` ";  $  sql .= "GROUP BY riderUid ";  $  sql .= "ORDER BY  `miles`  DESC"; 

I tried the statement in a couple other locations, without success.

result of echo $ sql; with the LIMIT statement back in:

SELECT riderUid AS `rider`,  ( SELECT SUM( mileage ) FROM `mileage`  WHERE riderUid = `rider` AND  YEAR(rideDate) = YEAR(CURDATE()) AND  MONTH(rideDate) = MONTH(CURDATE())  ORDER BY mileage DESC  LIMIT 5) AS `miles` FROM `mileage`  GROUP BY riderUid  ORDER BY `miles` DESC 

Addendum

Here’s the table structure:

Each row represents one rider/ride (riderUid/rideID).

What I want to do is to sum each rider’s mileage then return an ordered set of rows from most to least miles per rider (monthly or YTD).

A version of this would limit it to just the top n riders, which would be for a portable widget (one that could go on any page).

Currently I iterate through the rows and get the usernames, etc. from other tables. Ideally I’d like to do a JOIN in this query to get those, but I’m not a DBA and not skilled enough to do that. What I have here I harvested from SO, with a few modifications, and if anybody can suggest a better way to do it I’d be most grateful.