Top N records for each condition

[Benjamin_11]

I have the following data in a table called 'customers':

accmanid    custid  billone billltwo
1              1    126.9   514.6
1              2    264.73  108
1              3    130.5   514.6
1              4    137.82  514.6
2             10    126.9   514.6
2             11    126.9   375.48
2             12    126.9   117.55
2             13    126.9   261.66
3             19    130.5   117.55
3             20    130.5   117.55
3             21    264.73  117.55
3             22    165.43  117.55

and I am using the following query to retrieve the top three customers for 'accmanid' 1:

SELECT accmanid, custid, SUM(billone + billtwo) AS 
total FROM customers WHERE accmanid = 1 ORDER BY total DESC LIMIT 3

This outputs five rows ordered by the 'total' where accman = 1.

Here is my question:

How can I write the query to output three rows for EACH accmanid; Im not sure how to do such without the WHERE clause or some kind of loop, whcih Ive read is bad.

If you need more information or clarification, please ask and Il be happy to assist.

Thanks so much for your help and for this great site.

[diafol]

SELECT accmanid, custid, billone + billtwo AS total FROM customers WHERE accmanid = 1 ORDER BY total DESC LIMIT 5

SUM() is an aggregate (group) function - not what you want

[Benjamin_11]

Thanks for the feedback. That makes snese and I can make the correction, but I also need to query ALL of the 'accman'. Essentially I need to remove the WHERE clause and produce 5 rows of output for each 'accman'

[diafol]

SELECT accmanid, custid, total
FROM
(SELECT accmanid, custid, billone + billtwo AS total, 
              @grp_rank := IF(@current_accmanid = accman_id, @grp_rank + 1, 1) AS grp_rank,
              @current_accmanid := accmanid 
   FROM customers
   ORDER BY total DESC
 ) ranked

WHERE grp_rank <= 5;

Not tested

[Benjamin_11]

This is ALMOST there!

Its producing the five rows for each account manager as needed, but its duplicating the customer instead of having five seperate customers. Here is a summary of the output:

accmanid custid billone billtwo
1743    5347074 5278.05     0
1743    5347074 5278.05     0
1743    5347074 5278.05     0
1743    5347074 5278.05     0
1743    5347074 5278.05     0
1744    5138496 13495.86    0
1744    5138496 13495.86    0
1744    5138496 13495.86    0
1744    5138496 13495.86    0
1744    5138496 13495.86    0

Thanks so much for your help!

[diafol]

Ok I'll have a look.

[Benjamin_11]

Thanks so much; I appreciate your help.

[diafol]

This should do it...

SET @num := 0, @acc := '';

SELECT accmanid, custid, total
FROM (
   SELECT accmanid, custid, total,
      @num := IF(@acc = accmanid, @num + 1, 1) AS row_number,
      @acc := accmanid AS dummy
  FROM (SELECT accmanid, custid, billone+billtwo AS total FROM customers) AS t
  ORDER BY accmanid, total DESC
) AS X WHERE x.row_number <= 5;

Note there are two queries here. Setting up the user variables and then the main query.

Reference: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

[Benjamin_11]

Assuming they should run at the same time it is producing the duplicates as follows:

accman  custid  total
1743    5347074 5278.05
1743    5347074 5278.05
1743    5347074 5278.05
1743    5347074 5278.05
1743    5347074 5278.05
1744    5138496 13495.86
1744    5138496 13495.86
1744    5138496 13495.86
1744    5138496 13495.86
1744    5138496 13495.86
7966    5062809 10586.72
7966    5062809 10586.72
7966    5062809 10586.72
7966    5062809 10586.72
7966    5062809 10586.72

Should I be making any substitutions for 't' or 'X'?

[diafol]

I think I know what's wrong with your data - do you have an autoincrement PK field in the table? If not, this could be the reason for the repeat.

If you alter the table and add a PK/autoinc column - then check your data - is it still as you originally entered it or is it all duplicated as in your query output?

[Benjamin_11]

Here is the table:

CREATE TABLE `customers` (
  `rowid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `accmanid` int(11) NOT NULL,
  `custid` int(11) NOT NULL,
  `billone` decimal(13,2) NOT NULL DEFAULT '0.00',
  `billtwo` decimal(13,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`rowid`),
  KEY `custid` (`custid`),
  CONSTRAINT `custid` FOREIGN KEY (`custid`) REFERENCES `customerids`       (`custid`) ON DELETE NO ACTION ON UPDATE NO ACTION
  ) ENGINE=InnoDB AUTO_INCREMENT=24911 DEFAULT CHARSET=utf8;

[diafol]

OK - this works for me:

$q1 = "SET @num := 0, @acc := ''";
$q2 = "SELECT accmanid, custid, total
FROM (
   SELECT accmanid, custid, total,
      @num := IF(@acc = accmanid, @num + 1, 1) AS row_number,
      @acc := accmanid AS dummy
  FROM (SELECT accmanid, custid, billone+billtwo AS total FROM customers) AS t
  ORDER BY accmanid, total DESC
) AS X WHERE x.row_number <= 5";


$q = $pdo->query($q1);
$r = $pdo->query($q2);
$res = $r->fetchAll(PDO::FETCH_NUM);

echo "<pre>Query 1: $q1 <br />Return Type: " . ((gettype($q) == 'object') ? 'SUCCESS' : 'FAIL'). '<br />';
echo "Query 2: $q2 <br />Return Type: " . ((gettype($r) == 'object') ? 'SUCCESS' : 'FAIL') .  '</pre>';
echo "<table border='1'><tr><td>ACCMANID</td><td>CUSTID</td><td>TOTAL</td></tr>";
foreach($res as $row)
{
    echo "<tr><td>{$row[0]}</td><td>{$row[1]}</td><td>{$row[2]}</td></tr>";
}
echo "</table>";

Try it.