How to limit the result?

Jake K · #1 (**permalink**) 05-29-07, 08:50

I have a table as shown below:

CREATE TABLE `sales` (
`ID` int(11) PRIMARY KEY,
`SALES_PERSON` int(11) default NULL,
`PRODUCT` int(11) default NULL,
`MONTH` int(11) default NULL,
`AMOUNT` int(11) default NULL,
KEY `SALES_IDX1` (`SALES_PERSON`),
KEY `SALES_IDX2` (`MONTH`),
KEY `SALES_IDX3` (`PRODUCT`)
) ENGINE=MyISAM

I like to know top 3 products sold by each sales person on every month. What I am doing now is that select the DISTINCT SALES_PERSON in the table for a particular month and then iterate over each sales person in my java application level and applying one more query to get the final result. The queries are as follows:

First Query:
SELECT DISTINCT SALES_PERSON FROM SALES WHERE MONTH=5;

Second Query:
SELECT SUM(AMOUNT) AS SUMM, SALES_PERSON, MONTH, PRODUCT FROM SALES WHERE SALES_PERSON=102 GROUP BY SALES_PERSON, MONTH, PRODUCT ORDER BY SUMM DESC LIMIT 3;

Is it possible to achieve the same logic in a single query? I assume that a single query will give better performance.
Please note that we already optimized the query performance by adding indexes.
Please use the attached sample data if you need.

MySQL: MySQL 5.0.36-enterprise-nt
OS: Windows 2003 SP2
Application Language: Java
Connectivity : JDBC

Regards,
Jake

SAMPLE DATA:
1 101 1001 5 1000
2 101 1002 5 1000
4 101 1001 5 1000
3 101 1002 5 1000
5 102 1001 5 50
6 102 1002 5 50
7 102 1001 5 50
8 102 1002 5 50
9 101 1001 5 1000
10 101 1002 5 1000
11 101 1001 5 1000
12 101 1002 5 1000
13 102 1001 5 50
14 102 1002 5 50
15 102 1001 5 50
16 102 1002 5 50
17 101 1001 5 1000
18 101 1002 5 1000
19 102 1001 5 50
20 102 1002 5 50
21 101 1003 5 1000
22 101 1003 5 1000
24 101 1003 5 1000
23 101 1003 5 1000
29 101 1003 5 1000
30 101 1003 5 1000
31 101 1003 5 1000
32 101 1003 5 1000
37 101 1003 5 1000
38 101 1003 5 1000
25 102 1003 5 50
26 102 1003 5 50
27 102 1003 5 50
28 102 1003 5 50
33 102 1003 5 50
34 102 1003 5 50
35 102 1003 5 50
36 102 1003 5 50
39 102 1003 5 50
40 102 1003 5 50
41 101 1004 5 1000
42 101 1004 5 1000
44 101 1004 5 1000
43 101 1004 5 1000
49 101 1004 5 1000
50 101 1004 5 1000
51 101 1004 5 1000
52 101 1004 5 1000
57 101 1004 5 1000
58 101 1004 5 1000
45 102 1004 5 50
46 102 1004 5 50
47 102 1004 5 50
48 102 1004 5 50
53 102 1004 5 50
54 102 1004 5 50
55 102 1004 5 50
56 102 1004 5 50
59 102 1004 5 50
60 102 1004 5 50

r937 · #2 (**permalink**) 05-29-07, 09:21

try this --

Code:

select month
     , sales_person
     , product 
     , summ
  from (
       select month
            , sales_person
            , product 
            , sum(amount) as summ
         from sales 
       group 
           by month
            , sales_person
            , product 
       ) as t
 where ( select count(*)
           from (
                select month
                     , sales_person
                     , product 
                     , sum(amount) as summ
                  from sales 
                group 
                    by month
                     , sales_person
                     , product 
                )
          where month = t.month
            and summ > t.summ ) < 3

this can be simplified if you define a view for the GROUP BY subquery

aschk · #3 (**permalink**) 05-29-07, 09:26

Who wants to try a magical hacked MySQL specific analytic function?

Code:

SELECT sales_person,product,total,`month` FROM
  (SELECT if(@person = sales_person
          ,@rank := @rank + 1
          ,@rank := 1 + least(0,@person := sales_person)
         ) rank,total,sales_person,product,`month`
  FROM (
      SELECT sales_person,product,`month`,SUM(amount) as total
      FROM sales
      GROUP BY sales_person,product,`month`
      ORDER BY total DESC
  ) x, (SELECT @person := 0, @rank := 0) y
) z
WHERE rank < 4;

p.s. edited to include product ids...
p.p.s edited to include months