0

I have a table deals, and columns are name, amount, division, category. For each division category combination, I want to find top 3 entries with max amount. The final result should be in the format: name, division, category.

user1991452
  • 195
  • 1
  • 10
  • possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – Marc B Apr 18 '13 at 20:08

1 Answers1

0
   $db = new PDO($hostname,$username,$password);
   $query = 'Select name, division, category 
     From deals Where division = :division And category = :category 
     ORDER BY amount DESC Limit 3';

then create arrays of all your divisions and categories and loop them:

$top3s = array();
foreach($divisionArray as $division)
{
  foreach($categoryArray as $category)
  {
    $statement = $db->prepare($query);
    $statement->bindValue(':division', $division);
    $statement->bindValue(':category', $category);
    $statement->execute();
    $top3 = $statement->fetch(PDO::FETCH_ASSOC);
    $statement->closeCursor();

    array_push($top3s, $top3);
  }
}
print_r $top3s;
thelr
  • 1,134
  • 11
  • 30
Dan
  • 3,755
  • 4
  • 27
  • 38
  • Not a single query, but likely much more simple and readable than a single query for this particular question. – thelr Apr 18 '13 at 19:48
  • I dont understand your question? im using PDO mysql object which sanitizes input to mysql to prevent injection attacks. It is one of the industry standards yes. – Dan Apr 18 '13 at 20:26